Sql-server – Determine Which Parameters Passed Into Stored Procedure

sql serversql-server-2008stored-procedures

Is there a way to determine (from within a stored procedure) which parameters were passed into a stored procedure (vs. those which just took the default value)?

Best Answer

You can run a Profiler Trace using the default template. It will include the RPC:Completed Event. When you review the trace log you will see the values of the parameters passed and you can determine which were left out, therefore using the default values.

Related Solutions

Sql-server – How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql

Something like

WHERE
   (@p1 IS NULL OR SomeCol1 = @p1)
   AND
   (@p2 IS NULL OR SomeCol2 = @p2)
   AND
   (@p3 IS NULL OR SomeCol3 = @p3)
   AND
   (@p4 IS NULL OR SomeCol4 = @p4)

This will probably run slower then using separate SELECTs separated by IF. However, you do have 16 permutation. So I'd probably combined both techniques:

IF (common combination of NULL parameters #1)
 SELECT ...
ELSE IF (common combination of NULL parameters #2)
 SELECT ...
ELSE 
 SELECT using WHERE above

Sql-server – Is it possible to determine what part of a stored procedure is currently executing

You can follow along with either a SQL Server Trace or Extended Events.

You can watch completed statements and/or batches, and that should give you the granularity you are looking for.

SP:StmtCompleted Event Class

Best Answer

Related Solutions

Sql-server – How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql

Sql-server – Is it possible to determine what part of a stored procedure is currently executing

Related Question