Writing dynamic SQL queries

Question

I am having problems trying to create a fairly simple dynamic SQL query. The variables don't display the values they contain when I PRINT @SQLString. Any ideas?

    ALTER PROCEDURE [dbo].[usp_ItemSearch]
    @ItemNum varchar(30) = NULL
    ,@SearchFilter int
    ,@VendorNum varchar(10) = NULL
    ,@RecUserID int = NULL
    ,@StartDate smalldatetime = NULL
    ,@EndDate smalldatetime = NULL
AS
DECLARE @SQLString as varchar(1000)

SET @SQLString = 'SELECT RecID, VendorNum, VendorName, PORelNum, InvoiceNum, ItemNum, RecAddDate, LastUpdated FROM tbl_Processor_ItemDscLog'

IF @ItemNum IS NOT NULL
    BEGIN
        IF  @SearchFilter = 2
            BEGIN
                SET @SQLString = @SQLString + ' WHERE ItemNum LIKE ''%' + @ItemNum + ''''   --Ends with
            END
        IF  @SearchFilter = 1
            BEGIN
                SET @SQLString = @SQLString + ' WHERE ItemNum LIKE ''%' + @ItemNum + '%'''  --Contains
            END
        IF  @SearchFilter = 0
            BEGIN
                SET @SQLString = @SQLString + ' WHERE ItemNum LIKE ''' + @ItemNum + '%'''   --Starts with
            END
    END

IF @VendorNum IS NOT NULL
    BEGIN
        SET @SQLString = @SQLString + ' WHERE VendorNum = ''' + @VendorNum + ''''
    END

IF @RecSearchUserID IS NOT NULL
    BEGIN
        SET @SQLString = @SQLString + ' AND (RecAddUserID = @RecUserID)'
    END

IF (@EndDate IS NOT NULL)
    BEGIN
        IF (@StartDate IS NOT NULL) 
            BEGIN
                SET @SQLString = @SQLString +  ' WHERE RecAddDate between @StartDate AND @EndDate '
            END
        ELSE
            BEGIN
                SET @SQLString = @SQLString +  ' RecAddDate BETWEEN 01/01/1996 AND @EndDate + '
            END
    END

SET @SQLString = @SQLString +  ' ORDER BY ItemNum, VendorNum'
PRINT @SQLString

Is your PRINT @SQLString inside the stored procedure? (I can't see it) — Xavier Poinas
– Xavier Poinas, Commented Jun 23, 2010 at 3:01
There are good reasons why should avoid doing dynamic SQL in T-SQL. For example, what happens if @ItemNum is O'Malley? In general, do not do dynamic SQL this way. Do it in a middle-tier component as a parameterized query. — Thomas
– Thomas, Commented Jun 23, 2010 at 3:04
Can you give an example of middle tier? My front end is C# / ASP.Net — Frank
– Frank, Commented Jun 23, 2010 at 3:08
@user84850 - Even in a simple website, you should encapsulate your data calls into a single library or even a single class called by your code behind or controller. Right now, you are probably calling the above stored procedure. However, you could just as easily build a parameterized query using C# and call that instead of your stored procedure. — Thomas
– Thomas, Commented Jun 23, 2010 at 3:20

David · Accepted Answer · 2010-06-23 03:05:45Z

3

You are adding @ItemNum and @VendorNum to @SQLString correctly, but you aren't adding the other 3 variables correctly. You have to cast them as varchar's and concatenate them on to @SQLString the same way you are doing the other ones.

For example, here's how the @RecSearchUserID block should look:

IF @RecSearchUserID IS NOT NULL
    BEGIN
        SET @SQLString = @SQLString + ' AND (RecAddUserID = ' + CAST(@RecUserID AS VarChar) + ')'
    END

Also, you do need to be careful about your input variables containing apostrophes as the other commenters have pointed out in order to protect against SQL injection...

answered Jun 23, 2010 at 3:05

David

34.6k3 gold badges65 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Writing dynamic SQL queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related