Dynamic t-sql quotes in string

Question

Hi,

I have the following in a stored procedure:

DECLARE @new_column_name varchar(9)
DECLARE @table_name varchar(16)
DECLARE @SQLString nvarchar(2000)

SET @new_column_name = N'name'
SET @table_name = N'tbl_test_table'

SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @table_name + '] WHERE [' + @new_column_name + '] = ''' + @description + ''''``

This works absolutely fine until @description has a single quote in it. In my C# I replace single quotes with two single quotes but this is still causing a problem when creating the above SQL string.

Any ideas how to fix this for:

SET @description = N'Adam''s Car'

The reason I am using dynamic SQL is because the 'name' column is temporary and only exists during the lifetime of the stored procedure.

Answer 1

NB: See The Curse and Blessings of Dynamic SQL - Dealing with Dynamic Table and Column Names

You should still use parameterised sql and use exec sp_executesql (that takes parameters). Also use QUOTENAME around the object names rather than concatenating the brackets yourself.

SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM ' + 
QUOTENAME(@table_name) + ' WHERE ' + 
QUOTENAME(@new_column_name) + ' = @description'

EXECUTE sp_executesql @SQLString
    ,N'@description varchar(50), @CountOUT int OUTPUT'
    ,@description = @description
    ,@CountOUT = @CountOUT OUTPUT;