字符串动态T-SQL报价 [英] Dynamic t-sql quotes in string

问题描述

我在存储过程中的以下内容：

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 + ''''``

这工作绝对没问题，直到@description中有一个单引号。在我的C＃我有两个单引号代替单引号，但在创建上面的SQL字符串时，这仍然是导致问题

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'

我使用动态SQL的原因是因为在名称列是暂时的，存储过程的生命周期中只存在。

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

推荐答案

注意：请参见诅咒和祝福动态SQL - 使用动态表和列名处理

您还是应该使用参数化的SQL，并使用 EXEC sp_executesql的（即接受参数）。还可以使用 QUOTENAME 周围对象名称，而不是串联自己的括号内。

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;

这篇关于字符串动态T-SQL报价的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！