Error filtering dynamic sql

Posted on 2007-07-22
Last Modified: 2010-03-20
Dear Experts,

I have written a stored procedure using dynamic sql so we can take advantage of the new Row_Number() feature of sql server when sorting & paging our gridview controls.  It works wonderfuly until I want to add some filter parameters...  The @OfficeID parameter works fine, but when I try to pass in anything else it throws an error.  For example, if I send in 'David' for the parameter @ContactFirst, sql server would return the following error:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'David'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'David'.

I have been trying to fix this for far too long - after the 3rd hour I have finally gotten it to work without the filter parameters.  It's 6:45 AM (central) and I really need to go to bed.  I am posting this question in hope that someone with more experience than me can spot an error.  Please help!  I have added the stored procedure to a text document located so it doesn't get wrapped in this webpage.  I think it's easier to view is located here:

Thanks again,

Question by:stankstank
    LVL 39

    Accepted Solution

    try like this

    ALTER PROCEDURE [Contacts].[SEL_CONTACTS_GetContactList]
        @OfficeID int,
        @ContactFirst varchar(15) = '0',
        @ContactLast varchar(15) = '0',
        @LeadTypeID int = 0,
        @Address varchar(40) = '0',
        @PageSize  int = 5,
        @CurrentPage int,
        @SortExpression varchar(30)
          -- If no sort expression was entered, give the default.
          IF(LEN(@sortExpression) < 3) BEGIN SELECT @sortExpression = 'Primary_First' END

          DECLARE @sql nvarchar(4000)


          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;

          -- Generate the select statement
          SET @sql = 'SELECT  ContactID, Primary_First, Primary_Last, Address, PhoneHome, City, ZIP, Lead_Type, State
                            FROM      (SELECT c.Contact_Auto_ID as ContactID, Primary_First, Primary_Last, Address,
                                              PhoneHome, City, ZIP, Lead_Type, c.FK_State as State,
                                              ROW_NUMBER() OVER(ORDER BY ' + CONVERT(nvarchar(50),@sortExpression) + ') as RowNum
                                         FROM      [AppContacts].Contacts c
                                                          INNER JOIN [AppContacts].ContactsLeadTypes l ON c.Lead_Type_ID = l.Lead_Type_ID
                                         WHERE      c.FK_Account_ID = ' + CONVERT(varchar(5), @OfficeID) + '
                                                    AND ((''' + CONVERT(varchar(25),@ContactFirst)  + ''' = 0) OR (c.Primary_First = ''' + CONVERT(varchar(25),@ContactFirst) + '''))
                                                    AND ((''' + CONVERT(varchar(25),@ContactLast) + ''' = 0) OR (c.Primary_Last = ''' + CONVERT(nvarchar(25),@ContactLast) + '''))
                                                    AND ((' + CONVERT(varchar(3), @LeadTypeID) + ' = 0) OR (c.Lead_Type_ID = ' + CONVERT(nvarchar(3),@LeadTypeID) + '))
                                                    AND ((''' + CONVERT(varchar(40),@Address) + ''' = 0) OR (c.Address = ''' + CONVERT(nvarchar(40),@Address) + '''))
                                        ) as ContactInfo
                                        RowNum BETWEEN ' + CONVERT(nvarchar(10), @CurrentPage) +
                                        ' AND (' + CONVERT(nvarchar(10), @CurrentPage) + ' + '
                                        + CONVERT(nvarchar(10), @PageSize) + ') - 1'

          -- Execute the SQL query
          EXEC sp_executesql @sql

    LVL 39

    Expert Comment


    the problem is you have to enclose charater data within single quotes
    LVL 22

    Expert Comment

    The problem is in the way you are constructing your WHERE clause:
    WHERE c.FK_Account_ID = ' + CONVERT(varchar(5), @OfficeID) +
    AND ((' + CONVERT(varchar(25),@ContactFirst)  + ' = 0) OR (c.Primary_First = ' + CONVERT(varchar(25),@ContactFirst) + '))

    This gives you WHERE c.FK_Account_ID = 'MyOffice'
    AND (( David = 0 ) OR (c.Primary_First = David))

    FK_Account_ID cannot be null. Can the others that participate in the where clause?

    As appari said, doubling up single quotes is required.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    754 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

    22 Experts available now in Live!

    Get 1:1 Help Now