problem with syntax using SELECT CASE

Question

I get a syntax error using code like below...

select case @LocationID
        when 'ABC' then set @LinkToJob = 'some string'
        when 'XYZ' then set @LinkToJob = 'some other string'
        when '123' then set @LinkToJob = 'some other other string'
    end

error is: Incorrect syntax near the keyword 'set'

what am I missing? I basically need to set a the variable @LinkToJob to a different value based on what the variable @LocationID is... @LocationID is an nchar(3) and @LinkToJob is nvarchar(50).

Answer 1

Try the following

IF @LocationID = 'ABC' 
SET @LinkToJob = 'some string'
IF @LocationID = 'XYZ' 
SET @LinkToJob = 'some other string'
IF @LocationID = '123' 
SET @LinkToJob = 'some other other string'

---

Abdallah, PMP, MCTS

Answer 2

If you still want to use CASE, you can use it as follows

SELECT @LinkToJob = CASE 
	WHEN @LocationID = 'ABC' THEN  'some string'
	WHEN @LocationID = 'XYZ' THEN  'some other string'
	WHEN @LocationID = '123' THEN  'some other other string'
END

---

Abdallah, PMP, MCTS

Answer 3

Just remove the word set from your statement - otherwise it was correct.

---

Premature optimization is the root of all evil in programming.

Donald Knuth, repeating C. A. R. Hoare

My blog

Answer 4

Just remove the word set from your statement - otherwise it was correct.

---

Premature optimization is the root of all evil in programming.

Donald Knuth, repeating C. A. R. Hoare

My blog

Naomi, this is not right.
Check out the following

declare @locationid varchar(10)
declare @linktoJob varchar(max)
select @locationid = 'ABC'

select case @LocationID
        when 'ABC' then  @LinkToJob = 'some string'
        when 'XYZ' then  @LinkToJob = 'some other string'
        when '123' then  @LinkToJob = 'some other other string'
    end
<br/><br/><br/><br/><br/>

Abdallah, PMP, MCTS

Answer 5

You're right, I should have looked better.

It should be

select @LinkToJob = case ...

---

Premature optimization is the root of all evil in programming.

Donald Knuth, repeating C. A. R. Hoare

My blog

Answer 6

thanks Abdshall. I just used 3 'when' examples for the post but I actually have about a dozen different 'when' cases, in which case, in other programming languages I would go with a 'select case' or 'switch' construct, but seeing how the TSQL 'select case' works I'm actually going to opt for multiple IF statements. I mistakenly assumed it was the TSQL equivelent of the c# 'switch' or vb 'select case' construct.... which is does not appear to be and I don't like it, at least for this scenario, I realize it has other uses in TSQL.

thanks again.