CODEWITHSUNDEEP
sql-server-2008t-sql
Nate Pet
Nate Pet

Reputation: 46222

tsql dynamic sql best approach

The dynamica query I have below works but wondering if what I have below can be optimized or if there is a better way of doing it.

I have a web form where the user enters a location and Date Collected. For the date collected, I have a From Date Collected and To Date Collected. The user an leave the To date collected blank in which case it will do anything greater than the From Date Collected.

Note how I am doing the IS NOT NULL and 1=1 below. Also wondering if a dynamic SQL is the best approach or if there is a simpler way of doing this.

    DECLARE @sql varchar(max);

    SET @sql = 'SELECT * from tblProgram WHERE 1=1' 

    IF (@Location IS NOT  NULL)
    BEGIN
      SET @sql = @sql + ' AND Location = ' +  @Location
    END      

    IF (@FromDateCollected IS NOT  NULL AND @ToDateCollected IS NOT NULL)
    BEGIN
         SET @sql = @sql + ' AND pw.DateCollected >= ' +  QUOTENAME(convert(varchar, @FromDateCollected,101),'''') 
         + ' AND pw.DateCollected <= ' + QUOTENAME(convert(varchar, @ToDateCollected,101),'''')

    END
    ELSE IF (@FromDateCollected IS NOT  NULL AND @ToDateCollected IS  NULL)
    BEGIN
      SET @sql = @sql + ' AND pw.DateCollected >= ' +  QUOTENAME(convert(varchar, @FromDateCollected,101),'''') 

    END 

   exec(@sql)

Upvotes: 0

Views: 691

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

Well you can do as ta.speot.is comments use static SQL and do 

WHERE x is null or x > date_column?

However if you insist on using Dynamic SQL you should use a parameterized SQL statement using sp_executeSQL

Its easier to read, you don't have to use quotename, and you're protected from SQL Injection

DECLARE @Location int 
DECLARE @FromDateCollected datetime 
DECLARE @ToDateCollected datetime
SET  @ToDateCollected  = '1/02/2012'



DECLARE @sql nvarchar(max)
DECLARE @ParmDefinition nvarchar(max)

SET  @ParmDefinition = N'@Location int , @FromDateCollected datetime, @ToDateCollected datetime ';

SET @sql = N'SELECT * from tblProgram WHERE 1=1' 

IF (@Location IS NOT  NULL)
BEGIN
   SET @sql = @sql + N' AND Location = @Location'
END      

IF (@FromDateCollected IS NOT  NULL AND @ToDateCollected IS NOT NULL)
BEGIN
   SET @sql = @sql + N' AND pw.DateCollected >= @FromDateCollected '
      + N' AND pw.DateCollected <= @ToDateCollected '

END
ELSE IF (@FromDateCollected IS NOT  NULL AND @ToDateCollected IS  NULL)
BEGIN
   SET @sql = @sql + N' AND pw.DateCollected >= @FromDateCollected' 

END 

exec sp_executesql @SQL, @ParmDefinition, @Location = @Location,
                                  @FromDateCollected = @FromDateCollected,
                                      @ToDateCollected = @ToDateCollected

DEMO

Upvotes: 4

Related Questions