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?
  • So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement? – George.Palacios Nov 21 '18 at 13:22
  • Yes, that is true. – Anders Lindén Nov 21 '18 at 13:23
  • 2
    May we ask why? – user1008646 Nov 21 '18 at 14:33
  • To get one command per line resulting in a cleaner file. – Anders Lindén Nov 22 '18 at 20:33
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;
  • @AndersLindén yep, don't put a + before EXEC. – Erik Darling Nov 21 '18 at 13:46
  • I was trying to put the + in the argument to exec, building the sql string there – Anders Lindén Nov 21 '18 at 13:47
  • 1
    @Anders um, why? – Aaron Bertrand Nov 21 '18 at 13:56
  • The parameter of sp_executesql must be either a variable or a constant. No expressions allowed. – user1008646 Nov 21 '18 at 14:28
  • Is there no way of escaping characters in a t-sql string? – Anders Lindén Nov 21 '18 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, privacy policy and cookie policy

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