Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-09-25 : 15:40:46
|
I have a situation where I need to determine which type this record is. If type ID is 20, 23, or 25, then select F otherwise it is P. Here is a sample query to help explain my question. Select FirstName,Lastname,address, (If typeid = 20, 23, or 25 then F else P) as TypeDescFrom sometable The number(20,23,25) are hardcoded in the stored procedure and I have them in a variable becasue there are over 30 diffwerent numbers. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-25 : 15:45:42
|
Use case expression rather than IF Select FirstName,Lastname,address, CASE WHEN typeid IN ( 20, 23, 25) then F else P END as TypeDescFrom sometable Are F and P columns in sometable? If they are not, i.e., if they are literals, then use 'F' and 'P' with single quotes.If the list of type id's is in a variable, how is it stored? In a varchar variable with comma separating individual type ids, or perhaps in some other manner? |
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-09-25 : 16:25:16
|
This is what I have for ( 20, 23, 25) Delcare @var varchar(20)Set @var = '(20, 23, 25)' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-25 : 17:14:08
|
This:Select FirstName,Lastname,address, CASE WHEN ','+REPLACE(REPLACE(REPLACE(@var,')',''),'(',''),' ','')+',' LIKE '%,'+CAST(typeid AS VARCHAR(20)) + ',%' THEN F ELSE P END AS TypeDescFrom sometable |
|
|
|
|
|
|
|