Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server

SQL String Printing

Rate me:
Please Sign up or sign in to vote.
2.84/5 (8 votes)
22 May 2007CPOL 38.5K   21   6
Long SQL query printing.

Introduction

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:

SQL
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:

SQL
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.

SQL
exec Helper_LongPrint @SQLResult

The script for the Helper_LongPrint procedure is:

SQL
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 ) 
end
print @string

Please don't hesitate to ask any questions: ykocdag80@yahoo.com.

License

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


Written By
Architect
Turkey Turkey
Phd. Yildirim Kocdag is a Computer Engineer.

Programming Languages
Android, Objective-c, c#, vb.net, asp.net, javascript, TSQL.

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

ykocdag@yahoo.com

http://www.linkedin.com/profile/view?id=223886830

Comments and Discussions

 
BugBug Fix for loss of character when forcing a split Pin
jp_001-Aug-22 0:39
jp_001-Aug-22 0:39 
GeneralThis doesn't work - it cuts off the last character from the previous line Pin
sparky277815-Feb-11 5:09
sparky277815-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
sparky277815-Feb-11 5:14 
GeneralRe: This doesn't work - it cuts off the last character from the previous line Pin
jp_001-Aug-22 0:41
jp_001-Aug-22 0:41 
GeneralThanks Pin
ed_ward_graham4-Mar-09 8:31
ed_ward_graham4-Mar-09 8:31 
GeneralNice Pin
Wimmo22-May-07 9:06
Wimmo22-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.