Like statement

[Jim]

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

-Jim

[Simon Hayes]

"Jim" <ji********@motorola.com> wrote in message
news:72**************************@posting.google.c om...

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

-Jim

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

[Hugo Kornelis]

On 31 May 2004 00:13:07 -0700, Jim wrote:

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

-Jim

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)