help with where 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

SQL Server Development (2000)

help with where statement

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-01-04 : 15:07:11

declare @test varchar(50)
declare @filter varchar(50)
i am trying to set a part of a dynamic where statement
this is what i get.

any suggestions

set @filter='13'
set @test='and a.propadd like ' + @filter

select a.ordid,
a.metfileid,
a.propadd,
b.offname,
c.fname + ' ' + c.lname as customer
from tblorder a
inner join tblofficeprof b on a.moid=b.oid
inner join tblusercust c on a.ctuid=c.ctuid
where a.ordid not in (select ordid from tblorder_close) + @test

slow down to move faster...

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2002-01-04 : 15:18:22

You need use EXEC or sp_executesql. There's a lot of info on the site about executing dynamic sql, but basically you build a string of your entire sql statement and EXEC it.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-04 : 16:10:01

You can do like searches with out dynamic sql at all. CHeck out these:

Create table #Temp(Col1 VARCHAR(10))

Insert #Temp VALUES('abc')
Insert #Temp VALUES('bcd')

Declare @Search Varchar(10)

SEt @Search = 'abc'

Select * from #Temp Where Col1 like @Search

SEt @Search = '%bc'

Select * from #Temp Where Col1 like @Search

SEt @Search = '%bc%'

Select * from #Temp Where Col1 like @Search

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-04 : 19:05:53

Todd, I think the difference is that he's trying to concatenate a variable onto an explicitly written where clause.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources