short circuiting dynamic sql queries
Posted on 2008-11-17
I'm in a bit of a jiffy here...
I'm trying to short-cicuit a sql query here, where if the value in the where clause is null then it'll query all records else, query only the values in the where clause.
here's my code...
declare @filter as varchar(50)
declare @SQLCmd as varchar(4000)
set @filter = 'xxxx' -
set @SQLCmd = 'select * from table
where NOT ('+@filter+' IS NOT NULL and table.field <> '+@filter+' )'
this code works when @filter contains values on every executing. my problem is when i try to execute the code where @filter = null, @SQLCmd is null all in all..
ideally, the query should look like this:
select * from table
where NOT (null IS NOT NULL and table.field <> null )
can someone provide me with some tips?
subsequently, how do i make this work using a where... in clause?