2

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?
4
  • So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement? Nov 21, 2018 at 13:22
  • Yes, that is true. Nov 21, 2018 at 13:23
  • 2
    May we ask why? Nov 21, 2018 at 14:33
  • To get one command per line resulting in a cleaner file. Nov 22, 2018 at 20:33

2 Answers 2

6

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;
6
  • @AndersLindén yep, don't put a + before EXEC. Nov 21, 2018 at 13:46
  • I was trying to put the + in the argument to exec, building the sql string there Nov 21, 2018 at 13:47
  • 1
    @Anders um, why? Nov 21, 2018 at 13:56
  • The parameter of sp_executesql must be either a variable or a constant. No expressions allowed. Nov 21, 2018 at 14:28
  • Is there no way of escaping characters in a t-sql string? Nov 21, 2018 at 15:16
0

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 #TestProc\n(\n  @Param1 INT,\n  @Param2 NVARCHAR(128)\n)\nAS\nSET NOCOUNT ON;\n\nSELECT *\nFROM   tempdb.sys.sql_modules\nWHERE  [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 #TestProc2\n(\n  @Param1 INT,\n  @Param2 NVARCHAR(128)\n)\nAS\nSET NOCOUNT ON;\n\nSELECT *\nFROM   tempdb.sys.sql_modules\nWHERE  [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);
*/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.