praetorean
asked on
short circuiting dynamic sql queries
Hi,
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+' )'
exec (@SQLCmd)
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?
thanks
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+' )'
exec (@SQLCmd)
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?
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if there is only one filter then this will be better
IF @Filter is Null
select * from table
ELSE
select * from table where ield like '%'+@Filter+'%'
IF @Filter is Null
select * from table
ELSE
select * from table where ield like '%'+@Filter+'%'
declare @filter as varchar(50)
set @filter = 'xxxx' -
select * from table
where (@Filter IS NULL or field like '%'+@Filter+'%' )