Yet another SQL Server enthusiast

2015-03-16

How to see variable values in SQL Profiler ?

Filed under: debug,RAISERROR,sql server — sa @ 10:35 PM

For scalar variables, one answer is RAISERROR with a severity of 1 (which is equivalent to an warning) plus User Error Message event:

Code Snippet
CREATE PROCEDURE dbo.DoSomethingWithA
@a INT
AS
BEGIN
    DECLARE @b VARCHAR(100)
    SET @b = QUOTENAME(@a)

    — Get values for SQL Profiler
    PRINT '@b='
    PRINT @b
    RAISERROR('Variables: @a=%d, @b=%s', 11, 1, @a, @b)
    — End of Get values for SQL Profiler
END
GO

— Test
EXEC dbo.DoSomethingWithA 123
GO

SQL Profiler output:

Raiserror.Sql.Profiler.Variavle.Values

Note: this solution should be used only for debugging.

Note #2: It won’t work with user defined functions.

Advertisements

Blog at WordPress.com.