[Last Call] Learn how to a build a cloud-first strategyRegister Now


Error filtering dynamic sql

Posted on 2007-07-22
Medium Priority
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 anyway...it is located here:

Thanks again,

Question by:stankstank
  • 2
LVL 39

Accepted Solution

appari earned 2000 total points
ID: 19542341
try like this

    @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.

      -- 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

ID: 19542347

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

Expert Comment

ID: 19542988
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

831 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