short circuiting dynamic sql queries

Posted on 2008-11-17
Last Modified: 2012-05-05

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?

Question by:praetorean
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    You dont really need dynamic sql here

    declare @filter as varchar(50)
    set @filter = 'xxxx' -
    select * from table
    where (@Filter IS NULL or  field like '%'+@Filter+'%' )


    Accepted Solution

    i got it already... what i did was validate the value of the parameter... if = null then, i bypass and not add an additional where clause. if <> null, then i add an additional where clause...
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    if there is only one filter then this will be better

    IF @Filter is Null
         select * from table
         select * from table where ield like '%'+@Filter+'%'

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now