Complex dynamic SQL query

Question

I need help in resolving a complex SQL query. I am trying to build up the query one vlock at a time. One issue is: If a parameter for @PubNum is NULL, the query shows "..... where PubNum = '' which is an issue. What I need is if the parameter is NULL, then PubNum should not be in the where clause.

A second issue is:

• If @StartDate IS NOT NULL and @EndDate IS NOT NULL THEN RecAddDate BETWEEN @StartDate AND @EndDate
• If @StartDate IS NOT NULL and @EndDate IS NULL THEN RecAddDate BETWEEN @StartDate AND Today
• If @StartDate IS NULL and @EndDate IS NOT NULL THEN RecAddDate BETWEEN '01/01/2000' AND @EndDate
• If @StartDate IS NULL and @EndDate IS NULL THEN RecAddDate BETWEEN '01/01/2000' AND Today

Any ideas?

The complete query is:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_BookItemSearch]
    @BookSKU varchar(30) = NULL
    ,@SearchType int = NULL
    ,@PubNum varchar(10) = NULL
    ,@UserID int = NULL
    ,@StartDate smalldatetime = NULL
    ,@EndDate smalldatetime = NULL
AS
DECLARE @SQL as varchar(4000)

SET @SQL = 'SELECT RecID, PubNum, VendorName, InvoiceNum, BookSKU, RecAddDate FROM tb_BookInventoryLog]'

IF @BookSKU IS NOT NULL
    BEGIN
        IF  @SearchType = 2
            BEGIN
                SET @SQLClause = ' WHERE BookSKU LIKE ''%' + @BookSKU + ''''    --Ends with
            END
        IF  @SearchType = 1
            BEGIN
                SET @SQLClause = ' WHERE BookSKU LIKE ''%' + @BookSKU + '%'''   --Contains
            END
        IF  @SearchType = 0
            BEGIN
                SET @SQLClause = ' WHERE BookSKU LIKE ''' + @BookSKU + '%'''    --Starts with
            END
    END

IF @PubNum IS NOT NULL
    BEGIN
        IF @SQLClause IS NOT NULL
            BEGIN
                SET @SQLClause = @SQLClause + ' AND PubNum = ''' + @PubNum + ''''
            END
        ELSE
            BEGIN
                SET @SQLClause = @SQLClause + ' WHERE PubNum = ''' + @PubNum + ''''
            END
    END

IF @UserID IS NOT NULL 
    BEGIN
        IF @SQLClause IS NOT NULL
            BEGIN
                SET @SQLClause = @SQLClause + ' AND (UserID = ' + CAST(@UserID AS VarChar) + ')'
            END
        ELSE
            BEGIN
                SET @SQLClause = @SQLClause + ' WHERE (UserID = ' + CAST(@UserID AS VarChar) + ')'
            END
    END

If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
    BEGIN
            Set @SQLClause = @SQLClause + ' And (JoiningDate BETWEEN @StartDate AND @EndDate)'
    END

IF (@EndDate IS NOT NULL)
    BEGIN
        IF (@StartDate IS NOT NULL) 
            BEGIN
                SET @SQL = @SQL +  ' WHERE RecAddDate between' + CAST(@StartDate As smalldatetime) + ' AND ' + CAST(@EndDate as smalldatetime) + ''
            END
        ELSE
            BEGIN
                SET @SQL = @SQL +  ' RecAddDate BETWEEN 01/01/2000 AND @EndDate + '
            END
    END

SET @SQL = @SQL + @SQLClause + ' ORDER BY BookSKU, PubNum'
PRINT @SQL
--EXECUTE (@SQL)

Answer 1

I would conditionally include the filters if there were not null. Something like this:-

Set @WhereClause = 'Where 1=1'
If @PubNum is not null
  Set @WhereClause = @WhereClause + ' AND PubNum = ''' + @PubNum + ''''

Answer 2

Instead of doing dynamic SQL (which introduces a whole number of problems, and often isn't actually necessary), you could just use the parameters and some NULL checks as part of your WHERE clause using a couple of different techniques, depending on what you're trying to do. I tested this using SQL 2000/2005, and it works correctly (I'd also assume it's fine in 2008/R2).

ALTER PROCEDURE [dbo].[usp_BookItemSearch]
    @BookSKU varchar(30) = NULL
    ,@SearchType int = NULL
    ,@PubNum varchar(10) = NULL
    ,@UserID int = NULL
    ,@StartDate smalldatetime = NULL
    ,@EndDate smalldatetime = NULL
AS


  SELECT RecID, PubNum, VendorName, InvoiceNum, BookSKU, RecAddDate
    FROM [tb_BookInventoryLog]
   WHERE (
          (@BookSKU IS NULL) OR 
          (BookSKU LIKE CASE @SearchType
                          WHEN 0 THEN       @BookSKU + '%'
                          WHEN 1 THEN '%' + @BookSKU + '%'
                          WHEN 2 THEN '%' + @BookSKU
                        END
          )
         )
     AND ISNULL(@PubNum, PubNum) = PubNum
     AND ISNULL(CAST(@UserID AS VARCHAR), UserID) = UserID
     AND (
          (@StartDate IS NULL OR @EndDate IS NULL) OR
          (JoiningDate BETWEEN @StartDate AND @EndDate)
         )
     AND RecAddDate BETWEEN CASE
                              WHEN @EndDate IS NULL THEN RecAddDate
                              ELSE ISNULL(@StartDate, '01/01/2000')
                            END
                        AND ISNULL(@EndDate, GETDATE())
ORDER BY BookSKU, PubNum

Answer 3

For your second issue:

(@StartDate does not display the value of the parameter, instead displays ".....StartDate = @StartDate.....")

If you wish to keep using the dynamic SQL, consider replacing EXECUTE with sp_executesql. That stored procedure will accept parameters which you can then use inside your dynamic SQL (parameter substitution). For example:

Set @SQLClause = @SQLClause + ' And (JoiningDate BETWEEN @StartDate_PARAM AND @EndDate_PARAM)'

For the above, add @EndDate_PARAM as a parameter like so:

exec sp_executesql @SQL, N'@StartDate_PARAM DateTime, @EndDate_PARAM DateTime', @StartDate_PARAM = @StartDate, @EndDate_PARAM = @EndDate

It looks a little weird at first, but it's a good thing to do if you're going the dynamic SQL route.

You'll find more information on that here: http://msdn.microsoft.com/en-us/library/ms175170.aspx