Line-Continuation in T-SQL

Some (or maybe most?) languages and operating system command shells allow for breaking up long lines into multiple lines (i.e. lines separated by hitting Enter / Return). This is accomplished by ending a line with a particular character that indicates that the line is not ending. For operating system shells this means that the command is not yet submitted for execution until a line is ended without that special character. For example, in a DOS / Command Prompt window or CMD / BAT script, you would use a ^ (carrot) to continue the command onto the next line. In programming languages where commands are terminated (or completed) with a semi-colon or maybe even nothing (such as T-SQL where the ; is optional except when using WITH and THROW), line-continuation is used to break up long strings (text and binary).

Examples

While it is not widely used (at least I have never seen anyone besides myself use it), T-SQL does actually have a line-continuation character: \ (backslash). Placing a backslash at the end of a line within a string literal (or constant as the MSDN documentation refers to it) or binary string will ignore the newline after the backslash. For example:

PRINT N'Same
Line';

displays the following in the “Messages” tab:

But, add in the backslash (well, a space and then a backslash so that it looks right):

PRINT N'Same \
Line';

and now the following is displayed in the “Messages” tab:

For a binary string, if we try separating lines without a backslash:

PRINT 0x1234
5678;

we see the following error in the “Messages” tab:

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ‘5678’.

But, add in the backslash:

PRINT 0x1234\
5678;

and now the following is displayed in the “Messages” tab:

Using line-continuation is very helpful when dealing with very long strings. Not only can it make scripts more readable, but it might even make it easier to scroll through a script, depending on what editor is being used and how long the string is. I started using this back in 2008 for the SQL# installation script. At that time the SQL# Assembly was around 400 KB, which represented in text form is twice as large (1 byte in hex notation is 00 – FF, and so each byte requires 2 characters when represented in text). SQL Server Management Studio (SSMS) did not like those really long lines, and would hang for a few seconds each time I scrolled past it. Now, using line-continuation, really long binary strings are not a problem. All of the binary strings / literals in any of my examples make use of this, which can be seen in a textarea within the “SQLCLR vs. SQL Server 2017, Part 6: “Trusted Assemblies” – Whitelisted Assemblies can’t do Module Signing” post as well as any of the SQLCLR scripts in my PasteBin.

In order to make it easier to do this formatting, I created a command-line utility, BinaryFormatter (open-source and on GitHub), that reads in a binary file — .dll, .cer, .pvk — and creates a text file containing the hex representation of the binary file and splits it across multiple lines. I use this when creating Assemblies and Certificates.

Documentation

Now, according to the Microsoft documentation, SQL Server Utilities Statements – Backslash, the line-continuation character is not part of the T-SQL language, but instead a pre-processing feature of certain client tools:

SQL Server provides commands that are not Transact-SQL statements, but are recognized by the **sqlcmd** and **osql** utilities and SQL Server Management Studio Code Editor. These commands can be used to facilitate the readability and execution of batches and scripts.

and:

The backslash is not a Transact-SQL statement. It is a command that is recognized by the **sqlcmd** and **osql** utilities and SQL Server Management Studio Code Editor.

Well, this should be easy enough to test by placing some line-continuation into a Stored Procedure. A Stored Procedure is executed from the definition stored within SQL Server; there is no client tool pre-parsing whatever is within a Stored Procedure. And, just to be sure that the client tool that is being used to create the Stored Procedure does not somehow alter the Stored Procedure as it is being submitted to SQL Server, let’s obscure the request within Dynamic SQL that the Stored Procedure will build and execute at run-time.

I created the following Stored Procedure to do this test:

CREATE PROCEDURE #ThisLineGoesOnAndOnAndOn
(
  @UseBinaryString BIT = 0,
  @UseLineContinuation BIT = 0
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @LineContinuation NVARCHAR(5) = N'',
        @SQL NVARCHAR(200) = N'PRINT ',
        @CRLF NCHAR(2) = NCHAR(0x000D) + NCHAR(0x000A);

IF (@UseLineContinuation = 1)
BEGIN
  SET @LineContinuation = N'\';
END;

IF (@UseBinaryString = 0)
BEGIN
  SET @SQL += N'''Line1' + @LineContinuation + @CRLF + N'Line2'';';
END;
ELSE
BEGIN
  SET @SQL += N'0x1234' + @LineContinuation + @CRLF + N'567890;';
END;

PRINT @SQL;
PRINT '-----------------';

EXEC (@SQL);

END;
GO

You can see the tests and their results below:

EXEC #ThisLineGoesOnAndOnAndOn 0, 0;
/*
PRINT 'Line1
Line2';
-----------------
Line1
Line2
*/


EXEC #ThisLineGoesOnAndOnAndOn 0, 1;
/*
PRINT 'Line1\
Line2';
-----------------
Line1Line2
*/


EXEC #ThisLineGoesOnAndOnAndOn 1, 0;
/*
PRINT 0x1234
567890;
-----------------
Msg 102, Level 15, State 1, Line 61
Incorrect syntax near '567890'.
*/


EXEC #ThisLineGoesOnAndOnAndOn 1, 1;
/*
PRINT 0x1234\
567890;
-----------------
0x1234567890
*/

OR

We can also approach this from a different direction. Given that the documentation states that the backslash character is interpreted by the client program and is not known by SQL Server, then if that is true, the backslash (and the following newline) will be removed by the client program such that SQL Server only ever receives the single, continuous string. If the documentation is correct and the client program did not remove the backslash and following newline but instead passed them along to SQL Server, then SQL Server would return either a string with a backslash and newline (in the case of a character string), or an error (in the case of a binary string).

What the client program sends to SQL Server can be verified by using SQL Server Profiler or Extended Events. Capturing the actual T-SQL statement submitted to SQL Server will determine whether or not the client program is handling the line continuation or not. Along those same lines, the GO batch separator is truly a client-side feature and will not be seen in any captured T-SQL.

To set up SQL Server Profiler, be sure to capture either the SQL:StmtCompleted and/or the SQL:BatchCompleted events, and be sure to have the TextData column selected. Then, when executing either the PRINT or SELECT statements, you will indeed see the backslash character in the TextData column.

Conclusion

There you have it: the documentation is clearly wrong. The line-continuation character / feature is indeed part of the T-SQL language.

I will submit an edit to the documentation on GitHub to correct this.

---

UPDATE 2017-11-09

The correction has been submitted on GitHub via Pull Request (PR) # 212

UPDATE 2017-11-16

As of yesterday (Wednesday, November 15th), my edits to the “Backslash” page have gone live.