Sql Server: Include or print new line and carriage return using CHAR(13) + CHAR(10)
- Posted by Sqltimes
- On June 21, 2014
- 0 Comments
Quick one today:
Sometimes, during troubleshooting, PRINT commands come very handy in traversing the progress of procedure (or any batch of SQL code) execution.
When you have a large amount of code with hundreds of of lines of code, you might end up with many PRINT statements capturing the status.
The result might end up looking like this:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
---- Storing procedure execution progress in a status variable for troubleshooting--DECLARE @ProgressReport VARCHAR(8000)SELECT @ProgressReport = '1 : Start : ' + CONVERT(VARCHAR, GETDATE(), 109)SELECT @ProgressReport = @ProgressReport + '2 : @SV_Stats : ' + CONVERT(VARCHAR, GETDATE(), 109)SELECT @ProgressReport = @ProgressReport + '3 : Starting While : ' + CONVERT(VARCHAR, GETDATE(), 109)SELECT @ProgressReport = @ProgressReport + '@SVP_ID : ' + CONVERT(VARCHAR, 4654)SELECT @ProgressReport = @ProgressReport + '@SwitchOut_PartitionNumber : ' + CONVERT(VARCHAR, 54)SELECT @ProgressReport = @ProgressReport + '4 : Switch completed : ' + CONVERT(VARCHAR, GETDATE(), 109)SELECT @ProgressReport = @ProgressReport + '5 : Merge Completed : ' + CONVERT(VARCHAR, GETDATE(), 109)SELECT @ProgressReport = @ProgressReport + '6 : While Completed : ' + CONVERT(VARCHAR, GETDATE(), 109)PRINT @ProgressReportGO |
Non user friendly result
The result looks like this (not user friendly):
NewLine CarriageReturn Before
So, to make it pretty, or more readable, ‘new line’ & ‘carriage return’ characters come handy.
User friendly format
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
---- Storing procedure execution progress in a status variable for troubleshooting with CHAR(13) + CHAR(10)--DECLARE @ProgressReport VARCHAR(8000)SELECT @ProgressReport = '1 : Start : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)SELECT @ProgressReport = @ProgressReport + '2 : @SV_Stats : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)SELECT @ProgressReport = @ProgressReport + '3 : Starting While : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)SELECT @ProgressReport = @ProgressReport + CHAR(9) + '@SVP_ID : ' + CONVERT(VARCHAR, 4654) + CHAR(13) + CHAR(10)SELECT @ProgressReport = @ProgressReport + CHAR(9) + '@SwitchOut_PartitionNumber : ' + CONVERT(VARCHAR, 54) + CHAR(13) + CHAR(10)SELECT @ProgressReport = @ProgressReport + CHAR(9) + '4 : Switch completed : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)SELECT @ProgressReport = @ProgressReport + CHAR(9) + '5 : Merge Completed : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)SELECT @ProgressReport = @ProgressReport + '6 : While Completed : ' + CONVERT(VARCHAR, GETDATE(), 109) + CHAR(13) + CHAR(10)PRINT @ProgressReport GO |
NewLine CarriageReturn After
With CHAR(13) + CHAR(10) the result looks more user-friendly; Looks more like the actual code and its status at every step. Saves a lot of time in troubleshooting. From MSDN “CHAR can be used to insert control characters into character strings. The following table shows some frequently used control characters.”
|
Control character |
Value |
|---|---|
| Tab | char(9) |
| Line feed | char(10) |
| Carriage return | char(13) |
Hope this helps,
_Sqltimes
0 Comments