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.
