2010-06-22

Debugging Output in T-SQL

Here is a useful way to get debugging output while using SQL Server T-SQL:

DECLARE @message NVARCHAR(MAX)
SELECT @message = (
  SELECT something, andSomethingElse
  FROM someTable
  FOR XML AUTO
)
SELECT @message = REPLACE(@message, '><', '>'+CHAR(13)+'<')
RAISERROR(@message, 16, 1)

It is pretty cheesey, but it works in difficult contexts (e.g. restricted privileges, many tiers between you and the SQL server, unit tests that run in rolled back transactions).

at