Click here to Skip to main content
11,412,833 members (74,057 online)
Click here to Skip to main content

SQL String Printing

, 22 May 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
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:

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 ) 
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)

Share

About the Author

Yildirim Kocdag
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
Follow on   LinkedIn

Comments and Discussions

 
GeneralThis doesn't work - it cuts off the last character from the previous line Pin
sparky2778 at 15-Feb-11 6:09
membersparky277815-Feb-11 6:09 
GeneralRe: This doesn't work - it cuts off the last character from the previous line Pin
sparky2778 at 15-Feb-11 6:14
membersparky277815-Feb-11 6:14 
GeneralThanks Pin
ed_ward_graham at 4-Mar-09 9:31
membered_ward_graham4-Mar-09 9:31 
GeneralNice Pin
Wimmo at 22-May-07 10:06
memberWimmo22-May-07 10:06 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150427.1 | Last Updated 22 May 2007
Article Copyright 2007 by Yildirim Kocdag
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid