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)?

  • 3
    You could look at DBCC INPUTBUFFER(@@SPID); I suppose, but you'd have to parse, and this would be especially painful if you don't name your parameters. Otherwise, no, SQL Server doesn't differentiate between inherited value and declared value. If you have optional parameters, why not just make them NULL? You can always override NULL within the procedure using COALESCE(@param, your_default_value) and then you only really lose out on knowing whether the user explicitly passed NULL or didn't pass that parameter (you can test the parameter before you assign it a default value). – Aaron Bertrand Nov 17 '15 at 13:46
  • The obvious answer is that if you care about this, don't use default values? – AakashM Nov 19 '15 at 8:47
  • Aaron's comment should be the answer. – Thronk Nov 19 '15 at 19:02

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.

  • 1
    I don't believe this answers the question, or is particularly practical/possible to run from within the called procedure. – Mark Sinkinson Nov 17 '15 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.