Click here to Skip to main content
14,663,933 members
Articles » Database » Database » SQL
Posted 22 May 2007


20 bookmarked

SQL String Printing

Rate this:
2.84 (8 votes)
Please Sign up or sign in to vote.
2.84 (8 votes)
22 May 2007CPOL
Long SQL query printing.


Long queries bring more problems, and debugging or testing them is more complex than short ones.

Imagine that you write a procedure which includes an nvarchar value and the query is executed at the end of a procedure.

Let me illustrate this:

SET @SQLResult = @SQLResult + 'SELECT TOP(' + 
   Cast(@PageRowCount as nvarchar(20)) + ') #t_table.*, 
        @TotalRecordCount AS TotalRecordCount FROM #t_table ' 
SET @SQLResult = @SQLResult + ' INNER JOIN #OrderTable ON 
                 #OrderTable.NoticeBoardId = #t_table.NoticeBoardId ' 
SET @SQLResult = @SQLResult + ' where #OrderTable.rowNum > 
                 LTRIM(STR(' + convert(varchar,@CurrentRecordId) + ' )) ' 
SET @SQLResult = @SQLResult + ' ORDER BY #OrderTable.rowNum ASC ' 
Exec @SQLResult

If an error occurs while the Stored Procedure works, we can check it by writing code like this:

print @SQLResult

However, if the size of the @SQLResult variable is more than 4000 characters, it is cut and can not be shown in full. Therefore, a procedure should be used.

exec Helper_LongPrint @SQLResult

The script for the Helper_LongPrint procedure is:

CREATE PROCEDURE dbo.Helper_LongPrint( @string nvarchar(max) )AS SET 
   NOCOUNT ONset @string = rtrim( @string )declare @cr char(1), 
   @lf char(1)set @cr = char(13)set @lf = char(10) 
declare @len int,
@cr_index int, @lf_index int, @crlf_index int, 
@has_cr_and_lf bit, @left nvarchar(4000), @reverse nvarchar(4000) 
set @len = 4000 
while ( len( @string ) > @len ) begin 
set @left = left( @string, @len )
set @reverse = reverse( @left )
set @cr_index = @len - charindex( @cr, @reverse ) + 1
set @lf_index = @len - charindex( @lf, @reverse ) + 1
set @crlf_index = case when @cr_index < @lf_index 
then @cr_index 
else @lf_index end 
set @has_cr_and_lf = case when @cr_index < @len and @lf_index < @len 
then 1 
else 0 end 
print left( @string, @crlf_index - 1 ) 
set @string = right( @string, len( @string ) - @crlf_index - @has_cr_and_lf ) 
print @string

Please don't hesitate to ask any questions:


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Yildirim Kocdag
Turkey Turkey
Phd. Yildirim Kocdag is a Computer Engineer.

Programming Languages
Android, Objective-c, c#,,, javascript, TSQL.

Computer Science
DataMining, Compilers, Expert Systems, Digital Image Processing, AI and Extreme Programming.

Comments and Discussions

GeneralThis doesn't work - it cuts off the last character from the previous line Pin
sparky277815-Feb-11 5:09
Membersparky277815-Feb-11 5:09 
GeneralRe: This doesn't work - it cuts off the last character from the previous line Pin
sparky277815-Feb-11 5:14
Membersparky277815-Feb-11 5:14 
GeneralThanks Pin
ed_ward_graham4-Mar-09 8:31
Membered_ward_graham4-Mar-09 8:31 
GeneralNice Pin
Wimmo22-May-07 9:06
MemberWimmo22-May-07 9:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.