CODEWITHSUNDEEP
sqlsql-serverstored-procedures
francops henri
francops henri

Reputation: 517

how to change column name and change a value in a table via a stored procedure?

I have the table below :

Desk_name | head
ali       | john 
cars ln   | max
pol fr    | max
jus       | john

I want to create a stored procedure in order to have in return :

Name      | sector_head
ali mm    | john
cars ln   | max
pol fr    | max
jus mm    | john

As you can see the column name has changed (from 'Name' to 'Desk_name') and every name in the 'Name' column has the abbreviation "mm" at the end of each values if it is a single word (ali and jus).

How can I do such a stored procedure (i am using sql server)?

Thanks

Upvotes: 4

Views: 2778

Answers (4)

mao
mao

Reputation: 1404

As I understand, you need to rename column Desk_name before updating table. To do this you may try this:

sp_RENAME 'YourTableName.Desk_name' , 'Name', 'COLUMN'

It should rename your column. After that you should use Joe's or John's solution to update records in table.

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

In sql server

select case when charindex(' ',ltrim(rtrim(Desk_name)))=0 
            then Desk_name+' mm' 
            else Desk_name 
       end  as Name ,
       head as sector_head
from your_table


SQL fiddle demo

Upvotes: 4

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

SELECT CASE WHEN Desk_name = 'ali' OR Desk_name = 'jus'  THEN Desk_name + ' mm' ELSE Desk_name END AS Name, 
head AS sector_head
FROM DeskName

Upvotes: 0

John Woo
John Woo

Reputation: 263713

You need to supply ALIAS on it, example

SELECT CASE WHEN LTRIM(RTRIM(Desk_name)) LIKE '% %'  -- finds any space in string
            THEN Desk_name 
            ELSE Desk_name + ' mm'
       END AS Name, 
       head AS sector_head 
FROM   tableName

SQLFiddle Demo

Upvotes: 4

Related Questions