Building an SQL Statement in a stored procedure

[Paul McGann]

I have the following code SET @sql = 'SELECT * FROM tbl_Employee WHERE tbl_Employee.Department_ID = ' + @Department_ID + AND tbl_Employee.Surname LIKE %' + @Surname + '%' If i remove the Like part it works fine but with it added it's casuing some problems. I think I'm concatenating the sql wrong. If someone can help it would be much appreciated.

ASP all the way

[Lost User]

Do this and check your SQL:

print @sql

[Lost User]

The single quote before the AND keyword is missing, it should be:

SET @sql = 'SELECT * FROM tbl_Employee
WHERE tbl_Employee.Department_ID = ' + @Department_ID + ' AND tbl_Employee.Surname LIKE %' + @Surname + '%'

[Mycroft Holmes]

I presume there is a good reason why you are using dynamic SQL, the select statement does not require it! This will work without dynamic SQL

Set @Surname = @Surname + '%'

SELECT *
FROM tbl_Employee
WHERE tbl_Employee.Department_ID = @Department_ID
AND tbl_Employee.Surname LIKE @Surname

As Shameel suggested print the @SQL, copy the result back and try to run it, then fix it so it runs and make the changes to your dynamic sql construct.

[Paul McGann]

Thanks guys. The reason for dynamic sql is because this is part of a much larger section of code for custom paging. Again thanks

ASP all the way