mjhjmtu

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);
*/