Error filtering dynamic sql

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:  http://208.112.68.164/temp/procedure.txt

Thanks again,

Stank
stankstankAsked:
Who is Participating?
 
appariConnect With a Mentor Commented:
try like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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)
   )
AS
      -- If no sort expression was entered, give the default.
      IF(LEN(@sortExpression) < 3) BEGIN SELECT @sortExpression = 'Primary_First' END

      DECLARE @sql nvarchar(4000)

BEGIN

      -- 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
                        WHERE      
                                    RowNum BETWEEN ' + CONVERT(nvarchar(10), @CurrentPage) +
                                    ' AND (' + CONVERT(nvarchar(10), @CurrentPage) + ' + '
                                    + CONVERT(nvarchar(10), @PageSize) + ') - 1'

      -- Execute the SQL query
      EXEC sp_executesql @sql

END
0
 
appariCommented:

the problem is you have to enclose charater data within single quotes
0
 
JimBrandleyCommented:
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))
etc.

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

As appari said, doubling up single quotes is required.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.