RE: A Simple Introduction to Dynamic SQL

SSCertifiable

Points: 6522

February 13, 2018 at 11:19 am

#1979628

Chris Hurlbut - Monday, February 12, 2018 12:10 PM
   DECLARE @crlf CHAR(4)
    DECLARE @v_SQL varchar(MAX) = ''
    SET @crlf = CHAR(10) + CHAR(13)
    SELECT @v_SQL = @v_SQL + 'DELETE FROM dbo.Some_Table' + ' ' + @crlf    --can add carriage return line feed for real complex sql, makes it easier to debug
    SELECT @v_SQL = @v_SQL + 'WHERE Person = ' + CHAR(39) + 'JONES' + CHAR(39) --use char(39) instead of ''', easier to read especially if you are doint this alot
    PRINT @v_SQL

DECLARE @v_SQL Nvarchar(MAX) -- Should be Nvarchar DECLARE @QT Nchar(1) = NCHAR(39) -- declare the quote character as a variable rather than using the function later. SET @v_SQL = CONCAT( -- Using CONCAT can make it easier to write and read, especially for casts N'DELETE FROM dbo.Some_Table', @crlf, N'WHERE Person = ' , @QT, N'JONES', @QT, N';' )