asked on

MS SQL 2005 Update/Set/Case

I am trying to do away with dynamic sql that i have been using in a stored procedure to update columns dynamicly
i have converted the selects to use case without any problems, but the updates are proving a little more difficult

i am getting a syntax error near the keyword Case with the following code

can someone help me out with this
Thank you
I

declare @col varchar (max)
set @col = 'one'
update activity_history set  CASE @col
 
           WHEN 'one'  THEN one
           WHEN 'two'  THEN two
           WHEN 'three'  THEN three
           WHEN 'four'  THEN four
           WHEN 'five'  THEN five
           WHEN 'six'  THEN six
           WHEN 'seven'  THEN seven
           WHEN 'eight'  THEN eight
           WHEN 'nine'  THEN nine
           WHEN 'ten'  THEN ten
           WHEN 'eleven'  THEN eleven
           WHEN 'twelve'  THEN twelve
           WHEN 'thirteen'  THEN thirteen
           WHEN 'fourteen'  THEN fourteen
           WHEN 'fifteen'  THEN fifteen
           WHEN 'sixteen'  THEN sixteen
           WHEN 'seventeen'  THEN seventeen
           WHEN 'eighteen'  THEN eighteen
           WHEN 'nineteen'  THEN nineteen
           WHEN 'twenty'  THEN twenty
           WHEN 'twentyone'  THEN twentyone
           WHEN 'twentytwo'  THEN twentytwo
           WHEN 'twentythree'  THEN twentythree
           else  twentyfour
end

= 4

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

QuinnDester

ASKER

how do i do the

else twentyfour

there is only one value i cant easily identify, the else twentyfour is the perfect solution to it

QuinnDester

ASKER

can i update 2 columns like that, as they are related and the count column will need incrementing when ever the main column is updated..

would this be the right syntax

one = CASE @col WHEN 'one' THEN one + @status, count1 = count1 + 1 else one, count1 end
,two = CASE @col WHEN 'two' THEN two + @status, count2 = count2 + 1 else two, count2 end

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

JoeNuvo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

QuinnDester

ASKER

thanks... i have used the case on the selects, but the update statment using case was turning into much more code then using if and if statment like this

if @col = 'one' begin update activity_history set one = one + @status, count1 = count1 + 1 where Coordinates = @Coordinates end
if @col = 'two' begin update activity_history set two = two + @status, count2 = count2 + 1 where Coordinates = @Coordinates end

out of curiosity, am i correct in thinking that using a case like that in an update statment it will execute every line of code, if it doesnt match it updates with the else..

where using the if, if it doesnt match it skips the code and goes on to the next if.

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

QuinnDester

ASKER

my bigest problem with dynaimic sql was being able to use variables that needed setting using the dynamic sql... or even reusing ones already set after dynaimc sql had executed,

QuinnDester

ASKER

shared the points as all comments helped me arrive at my solution, thank you for your input