在动态SQL中添加撇号 [英] Adding an apostrophe into a dynamic SQL

问题描述

我想问一下如何在动态SQL中添加撇号。我需要在其中必须带有撇号的一列中返回一条SQL语句。

I would like to ask how can I add an apostrophe into a dynamic SQL. I need to return an SQL statement in one of the columns which has to have apostrophes in itself.

我有以下语句：

SET @SQL_String = N'INSERT INTO #ReturnTable
    (
    TableName,
    ColName,
    SQL_Statement,
    Value
     )
VALUES
    (
    ''' + @TableName + ''',
    ''' + @ColName + ''',
    ''' +
         'SELECT ' + 
             @ColName +
         ' FROM ' +
             @TableSchema + '.' + @TableName +
         ' WHERE ' + 
             @ColName + ' = ' + CAST(@GuidArgument AS NVARCHAR(50)) + ';' +''',
        (
        SELECT
            ' + @ColName + '
        FROM
            ' + @TableSchema + '.' + @TableName +
       ' WHERE '
            + @ColName + ' = ''' + CAST(@GuidArgument AS NVARCHAR(50)) + 
       '''))';

执行方式：

EXECUTE @RC = [dbo].[GetLocationOfGuidPre] 'F2CAB996-F00F-43B8-A67A-0000721A829D'

我需要将整个第一个CAST放入一对'。

I need to put a whole first CAST into a pair of '.

我尝试过：

• 将整个 CAST 语句放入一个分隔的变量中，例如： DECLARE @Test NVARCHAR（50）;
  
  
  
  
  • SET @Test = CAST（@GuidArgument AS NVARCHAR（50））;
  
  
  • SET @Test ='CAST（@GuidArgument AS NVARCHAR（50））';
  
  
  • SET @Test ='''CAST（@GuidArgument AS NVARCHAR（50））''';
  
  
  • Putting whole CAST statement into a separeted variable like: DECLARE @Test NVARCHAR(50);
    • SET @Test = CAST(@GuidArgument AS NVARCHAR(50));
    • SET @Test = 'CAST(@GuidArgument AS NVARCHAR(50))';
    • SET @Test = '''CAST(@GuidArgument AS NVARCHAR(50))''';
    
    
    • 'WHERE'+ @ColName +'= '''+ CAST（@GuidArgument AS NVARCHAR（50））+''';'
    
    
    • ' WHERE ' + @ColName + ' = ''' + CAST(@GuidArgument AS NVARCHAR(50)) + ''';'

    推荐答案

    请使用CHAR（39）而不是在动态代码中直接输入'。
    示例：

    Please use CHAR(39) instead of typing ' in your dynamic code directly. Example:

    declare @my_dynamic_sql nvarchar(max) = 'print char(39);';
    exec(@my_dynamic_sql);
    

    这篇关于在动态SQL中添加撇号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！