View parameter values on currently running procedure?
parameters, t-sql
Solution
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.
Problem
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?