Dynamic Case Statement - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Case Statement

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-12-16 : 13:34:18

I would like to create a dynamic case statement , but it keep giving me an error: Incorrect syntax near '='. Can I do something like this? or is there any other ways?

Declare @type varchar
Declare @code int
Set @type = ‘School’
Set @code = ‘1111’

Select * from load l
Where l.code = @code And
case
when @type = ‘School’
Then l.procedure_code in ( ‘7654’,’4567’, ‘7898’)
End

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-16 : 13:39:34

[code]
Select * From load l
Where l.code = @code
And
(l.procedure_code in ('7654','4567','7898'
or
@type = 'School')
[/code]

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-16 : 13:51:56

close !


Select * From load l
Where l.code = @code 
And 
	(l.procedure_code in ('7654','4567','7898')
	or
	@type <> 'School')

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-16 : 13:56:24

oops

Corey

dsdeming

479 Posts

Posted - 2004-12-16 : 13:59:05

This way worked for me.

SELECT * FROM load l
WHERE l.procedure_code IN( CASE WHEN @type = 'School'
THEN '7654,4567,7898'
ELSE l.procedure_code
END )
AND l.code = @code

Dennis

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-12-16 : 14:00:39

Your solution won;t work because @type can be different value
Declare @type varchar
Declare @code int
Set @type = ‘School’ ( or @type = 'Home' or @Type = 'Institute', ...)
Set @code = ‘1111’

Select * from load l
Where l.code = @code And
case
when @type = ‘School’
Then l.procedure_code in ( ‘7654’,’4567’, ‘7898’)
when @type = 'Home'
then l.l.procedure_code in ( ‘1111’,’2222’, ‘333’)
Else '0'
End

Can I use a case statement in the WHERE clause so it can be filtered the right procedure_code out for different @type?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-16 : 14:05:07

quote:
Originally posted by dsdeming

This way worked for me.

SELECT * FROM load l
WHERE l.procedure_code IN( CASE WHEN @type = 'School'
THEN '7654,4567,7898'
ELSE l.procedure_code
END )
AND l.code = @code

Dennis

That doesn't work. Did you try it with actual data? IN('some string expression') doesn't automatically parse the string into multiple values!

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-16 : 14:08:02

As for the question, please see:

http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

in your example,

Select * from load l
Where l.code = @code And
(@type <> ‘School’ or l.procedure_code in ( ‘7654’,’4567’, ‘7898’)) AND
(@type <> 'Home' or l.l.procedure_code in ( ‘1111’,’2222’, ‘333’))

by the way, if this logic is persistant through your data, you are much better off creating a table with these values:

School,7564
School,4567
School,7898
Home,1111
Home,2222
Home,3333

and simply joining to the table. Let the DATA drive your SELECT statemements, not hard-coded values in WHERE clauses in which you list values in your tables.

- Jeff

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-12-16 : 14:44:12

Thanks, Jeff!!!

Marry Ch

ristmas!

dsdeming

479 Posts

Posted - 2004-12-16 : 16:07:28

It did actually work for me. I created a temp table, loaded it with data, and I got back exactly the data I expected to.

Dennis

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-16 : 17:32:46

not be argumentative, but no it didn't work! As we've pointed out over and over here at SQLteam, WHERE something IN('some single string value') does NOT work !!! the single string value is NOT parsed into multiple values magically just because the string contains commas!

what do you think this should return:

select * from table where PersonName in ('Smith, Jeff')

does that return rows in which personName is either Smith or Jeff, or only rows in which it is equal to 'Smith, Jeff' ??



create table [load] (code int , Procedure_Code varchar(10))

insert into [load]
select 1, '7654' union
select 1, '4567' union
select 1, '7898' union
select 1, '1234'

go

declare @code int;
declare @type varchar(10);


set @code = 1
set @type = 'school'

-- this does not work;
-- it is your code, pasted right in:

SELECT * FROM [load] l
WHERE l.procedure_code IN( CASE WHEN @type = 'School'
THEN '7654,4567,7898'
ELSE l.procedure_code
END )
AND l.code = @code

-- this does work:

SELECT * FROM [load] l
WHERE l.code = @code and
  (@type <> 'school' or Procedure_Code in ('7654','4567','7898'))

go
drop table [Load]

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources