65.9K
CodeProject is changing. Read more.
Home

SQL String Printing

starIconstarIcon
emptyStarIcon
starIcon
emptyStarIconemptyStarIcon

2.84/5 (8 votes)

May 22, 2007

CPOL
viewsIcon

39586

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.