CODEWITHSUNDEEP
t-sqlparameters
Chad Baldwin
Chad Baldwin

Reputation: 2602

View parameter values on currently running procedure?

Using Transact SQL

Just curious, is there a way to view the values of a parameter (aka, the EXEC line run to execute the proc?) of a proc that is currently in the process of running?

Example, I run: EXEC HelloWorld @SQL = 1

Is there a table or log or anything I can look at WHILE the proc is still running, and see @SQL = 1?

Upvotes: 3

Views: 5297

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48776

The best way to get the parameter values passed into procs, especially when executed remotely, is via SQL Server Profiler or Extended Events. If using SQL Server Profiler, you need to capture the following events:

  • (in Stored Procedures)
    • RPC: Completed
    • SP: Completed
  • (in TSQL)
    • SQL: BatchCompleted

And you will see the values in the "TextData" field (so you need to select that column for all 3 of those events).

Upvotes: 3

George Mastros
George Mastros

Reputation: 24498

While a procedure is running, you can execute a DBCC INPUTBUFFER command in another window. You will need to know the SPID that is executing your HelloWorld procedure.

If you are running HelloWorld within SQL Server Management Studio, you can see the SPID shown on the status bar at the very bottom of the window. My IDE shows 6 panels on the status bar. The 3rd panel shows the login name with the SPID in parenthesis. Example "YourDomain\YourLogin (59)". The 59 is the SPID you are looking for.

If you are not running the query in SQL Server Management Studio and do not have the SPID readily available, you can execute the following command:

sp_who2

This will show a result set with a row for every connection to the SQL Server Instance. Any SPID below 50 represents an internal process. Anything greater than 50 is a user connection. Based on the information you see in this result set, hopefully you will be able to determine the SPID that is executing HelloWorld.

Once you know the SPID, you can see the command it is currently executing by issuing the following command in a new query window.

DBCC INPUTBUFFER(59)

You will want to replace the 59 above with the actual SPID that you previously determined. Executing the command above will show you the command that is currently executing, including the parameter values.

Upvotes: 3

Vladimir Semashkin
Vladimir Semashkin

Reputation: 1280

There is no way to get parameter value from dynamic view of functions. But in your case I can recommend you to use the following code inside your procedure:

declare @SQL int = 1
raiserror('@SQL = %i',0,0,@SQL) WITH NOWAIT

WITH NOWAIT is very important thing. With these words won't wait information like if you use print

Upvotes: 2

Related Questions