I need to build a bunch of dynamic like statements '% ' or '% %'.

heres an example of one that im working on just to test..

declare @check varchar(50)
set @check = 'Bad'
declare @select varchar(50)
set @select = 'select * from art where style like %' + @check + ' and
inventoryid =13'

exec (@select)


how can I make this work for either like statements?

thanks

[quoted text, click to view]

You don't need dynamic SQL here:

select *
from art
where style like '%' + @check
and inventoryid = 13

One possible solution is to use an additional variable to indicate whether
or not to use both % delimiters:

declare @full bit
set @full = 1 -- 1 for both delimiters, 0 for one

select *
from art
where style like '%' + case @full when 1 then @check + '%' else @check end
and inventoryid = 13

If you have a lot of similar queries, you might want to look at using
full-text searching, which may be more efficient - MSSQL can't use an index
on the style column to help with the queries above.

Simon

[quoted text, click to view]

Hi Jim,

The query you build misses some quotes. There are two ways to solve this.

1. Without dynamic SQL (preferred, unless you have a very good reason to
use dynamic SQL):

declare @check varchar(50)
set @check = 'Bad'
select * from art where style like '%' + @check + and inventoryid =13


2. With dynamic SQL:

declare @check varchar(50)
set @check = 'Bad'
declare @select varchar(50)
set @select = 'select * from art where style like ''%' + @check + ''' and
inventoryid =13'

exec (@select)


Best, Hugo
--