Click here to Skip to main content
13,044,428 members (126,317 online)
Click here to Skip to main content
Add your own
alternative version

Stats

22.6K views
19 bookmarked
Posted 22 May 2007

SQL String Printing

, 22 May 2007
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

You may also be interested in...

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.

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