Case statement

[jasperjasper]

In T-SQL I am confused... How would this work in a Cas statement..

Suppose I am testing a variable @Amp
If its equal to 'VP' I want to do some things
IF its equal to 'T' something else

Thanks

 

[twifosp]

You can find syntax for CASE in BOL (books online).

Type CASE into query analyzer and highlight it and press shift-F1, for an auto search.

Basically:

CASE
when @amp = 'vp' then <insert code here>
when @amp = 't' then <you get the idea>
else <more ambigious stuff here>
END

 

[SQLSister]

Depends on what you want to do. If you want to display a calculated value or specific text or something inthe nature of one step from within a select, then Case is the right command. But if you want to perform mulitple procedural steps, then perhaps you need the IF statement.

So you can use case within a select like so:

Select field1, case  @var when 'a' then 'Good'
                  when 'b' then 'Bad'
        End as Status from table1

Questions about posting. See faq183-874

 

[jasperjasper]

Thanks ....
I dont want to use it in a select statement, I want to use it in place of an IF like in VB,,,, can it be used that way

 

[jasperjasper]

SQL Server still does not like what I am doing,,,

THis is the code i have


CASE @AmpAcctType
when @AmpAcctType = 'vp' then
BEGIN
Set @AmpAccount = @NewLocationNum
Set @NewLocationNum = @NewLocationNum + 1
-- insert the vendor pickup into the many to many table here
INSERT into VendPickups(Vendor, Pickup)
Values (@bkAccount, @mcAccount )
END

END

What am I doing wrong?
Thanks for your help. Ed

 

[SQLSister]

Use an IF statement instead, case can only be used within a t-sql Statement not used for procedural control.

Questions about posting. See faq183-874

 

[jasperjasper]

THanks ....its the old KISS!!!