When you're debugging a dynamic SQL query, it can be helpful help to output the final query onto the message window. The main problem with this is that the PRINT command has a limit of 4000 unicode characters, as demonstrated here:
DECLARE @seqno AS int = 0
DECLARE @sql AS nvarchar(MAX)
SET @sql = 'SELECT ' + CAST(@seqno AS varchar) + ' AS sequence'+ CHAR(10)
WHILE (@seqno < 1000)
BEGIN
SET @seqno = @seqno + 1
SELECT @sql = @sql + 'UNION SELECT ' + CAST(@seqno AS varchar) + CHAR(10)
END
PRINT @sql --Limited to 4000 characters.
The string can be outputted in 4000 character chunks, but because each PRINT command adds it's own new-line character, the final result can appear chopped up and may be invalid. To overcome this, the following method finds an existing new-line character and uses it as a break off point:
DECLARE @seqno AS int = 0
DECLARE @sql AS nvarchar(MAX)
SET @sql = 'SELECT ' + CAST(@seqno AS varchar) + ' AS sequence'+ CHAR(10)
WHILE (@seqno < 1000)
BEGIN
SET @seqno = @seqno + 1
SELECT @sql = @sql + 'UNION SELECT ' + CAST(@seqno AS varchar) + CHAR(10)
END
/* Print entire statement */
DECLARE @SqlLength int = 0
DECLARE @CutOff int = 0
WHILE @SqlLength < LEN(@sql)
BEGIN
SET @CutOff = 4000 - CHARINDEX(CHAR(10),REVERSE(SUBSTRING(@sql, @SqlLength, 4000)))
PRINT SUBSTRING(@sql, @SqlLength, @CutOff)
SET @SqlLength = @SqlLength + @CutOff + 1
END
/* END Print entire statement */
EXEC sp_executesql @sql
