Cfrtjryk

I want to put code in a single line that will create a stored procedure that contains newlines.

• Is that possible?


• Do I need to use sp_executesql?


• How do I escape newlines in a sql statement?


• How do I escape newlines in a string?

Yes, you can do something like this with dynamic SQL:

DECLARE @SQL NVARCHAR(MAX) = N'';

DECLARE @NewLine NCHAR(1) = NCHAR(10);



SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' + @NewLine + N'WHERE d.database_id > 4;' + @NewLine;



PRINT @SQL;

EXEC sys.sp_executesql @SQL;

No, T-SQL cannot escape newlines, tabs, etc. It only has an escape sequence for embedded string delimiters: '' = '. There is also an escape sequence for delimited identifiers: either "" for ", or ]] for ], depending on which one is being used to delimit the identifier.

If you want to work with escape sequences, you can do so manually via the REPLACE function.

Depending on how the "single line of code" is being interpreted, you can do either:

IF (OBJECT_ID(N'tempdb..#TestProc') IS NOT NULL)

BEGIN

    DROP PROCEDURE #TestProc;

END;

GO



-- The following is technically a single line (with multiple commands):

DECLARE @SQL NVARCHAR(MAX) = N'CREATE PROCEDURE #TestProcn(n  @Param1 INT,n  @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM   tempdb.sys.sql_modulesnWHERE  [object_id] = OBJECT_ID(N''tempdb..'' + @Param2);'; SET @SQL = REPLACE(@SQL, N'n', NCHAR(10)); EXEC (@SQL);



-- View the definition:

PRINT @SQL;



-- Test the proc:

EXEC #TestProc 2, N'#TestProc';

Or, if you can only execute a single command/statement, then you can try the following:

IF (OBJECT_ID(N'tempdb..#TestProc2') IS NOT NULL)

BEGIN

    DROP PROCEDURE #TestProc2;

END;

GO



-- The following is a single line and a single statement:

EXEC (N'DECLARE @SQL NVARCHAR(MAX) = N''CREATE PROCEDURE #TestProc2n(n  @Param1 INT,n  @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM   tempdb.sys.sql_modulesnWHERE  [object_id] = OBJECT_ID(N''''tempdb..'''' + @Param2);''; SET @SQL = REPLACE(@SQL, N''n'', NCHAR(10)); EXEC (@SQL);');



-- Test the proc:

EXEC #TestProc2 2, N'#TestProc2';



-- View the definition:

DECLARE @SQL2 NVARCHAR(MAX);

SELECT @SQL2 = [definition]

FROM   tempdb.sys.sql_modules

WHERE  [object_id] = OBJECT_ID(N'tempdb..#TestProc2');



PRINT @SQL2;

-- returns (in "Messages" tab):

/*

CREATE PROCEDURE #TestProc2

(

  @Param1 INT,

  @Param2 NVARCHAR(128)

)

AS

SET NOCOUNT ON;



SELECT *

FROM   tempdb.sys.sql_modules

WHERE  [object_id] = OBJECT_ID(N'tempdb..' + @Param2);

*/