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]

Re: Like statement


"Jim" <jim.ferris@mot orola.com> wrote in message
news:729757f9.0 405302313.2b041 2bb@posting.goo gle.com...[color=blue]
> 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[/color]

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]

Re: Like statement

On 31 May 2004 00:13:07 -0700, Jim wrote:
[color=blue]
>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[/color]

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)