Using Case and Like together - 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

General SQL Server Forums

New to SQL Server Programming

Using Case and Like together

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2011-12-29 : 10:33:20

Hi All,

I am trying to write a CASE statement depending on what is passed in the variable @vendor_type.

Declare @vendor_type varchar(60)

select @vendor_type = 'Keeltext'

select product_id,
order_id,
case when @vendor_type ='Keeltext' then name_data like'%keel%'
when @vendor_type ='Corell' then name_data like '%corell%'

All help is appreciated.

Thank you,
Petronas

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 10:39:00

Would this work for you?


SELECT
	product_id,
	order_id,
	name_data
FROM
	yourTable
WHERE
	name_data LIKE '%'+@vendor_type+'%';

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 13:49:52

I THINK (and I hate when that happens) that they want a dynamic table name

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

revdnrdy
Posting Yak Master

220 Posts

Posted - 2011-12-29 : 15:03:38

quote:
Originally posted by sunitabeck

Would this work for you?

SELECT
product_id,
order_id,
name_data
FROM
yourTable
WHERE
name_data LIKE '%'+@vendor_type+'%';

Also..

Doesn't using wildcards like this cause the engine to disregard indexes? I think you end up getting a full table scan in your query which could slow you down performance wise.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 15:10:31

of course it does...anyone know what non-sargable means?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources