How can I dump all parameters of a stored procedure into an error log?

Posted on 2014-01-07
Last Modified: 2016-08-18
Currently in all our procedures we use the same template for error catching.  We use a function to store the error message, severity, state, and store procedure name into a log table.  This has helped us tremendously during testing and production but find it is still lacking.  When there is an issue in production, we need to sometimes track down a user and find out what data was entered to see what we missed/overlooked (it happens).  

We have a code snippet that shows us the list of parameters for any given stored procedure here:
SELECT AS Parameter,    AS [Type]
FROM sys.procedures sp
JOIN sys.parameters p 
    ON sp.object_id = p.object_id
JOIN sys.types t
    ON p.system_type_id = t.system_type_id
WHERE = 'Insert_Order'

Open in new window

Now we need a way (maybe using dynamic sql?) to dynamically get the values of all the parameters and dump it into a string (@orderdate='1/1/99',@orderpurchasenumber='12345',@orderanything='something',etc)

Here is our current sql catch statement:
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;
		DECLARE @ObjectName NVARCHAR(100);

				@ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ObjectName = OBJECT_NAME(@@PROCID); -- To get the current object name which raise the error 
		--Log the Error
		EXEC dbo.spLog_Application_Error 'SYSTEM', @ErrorMessage, @ErrorSeverity, @ErrorState, @ObjectName --Added by Adriano
		SELECT -1		

Open in new window

Question by:chrisjmccrum
  • 3
  • 3
  • 2
  • +2
LVL 16

Expert Comment

by:Surendra Nath
ID: 39762857
This not possible dynamically.
Atleast as far as I know.

In one of my projects, we pass the parameters of the stored procedure concatenated with thier names or seperated by commas to the error handling stored proc.
Or put them into the log table manually in the catch block...

Unfortunately I think there is no other way.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39762905
Why not add the parameter names and values to an Xml variable that can then be passed to the standard Stored Procedure that does all the logging?

Author Comment

ID: 39763335
There has to be way haha, I'm doing some weird dynamic sql statements at the moment to try and iron this out.  I'd rather not have to rely on the human variable of someone forgetting a parameter in the procedure that was updated or inserted.  I guess I just feel like if I have the list of parameters and their data types that I should be able to fill a varchar(max) variable with a running list of the parameters and their values.

Maybe I'm asking too much from SQL haha...
LVL 16

Expert Comment

by:Surendra Nath
ID: 39763373
At this moment there is no such functionality with SQL Server, nor it is planned for future releases.
LVL 69

Accepted Solution

ScottPletcher earned 500 total points
ID: 39763776
The overhead and complexity of doing it dynamically makes doing that impractical to me.

This is one of those cases where I suggest dynamically generating static code.  That is, run the code below stand-alone when a proc is created or params are modified, and insert the resulting code into the error code where you need to capture param values.  After the code runs, the variable @param_values will contain the requested string with all the param values.

--run this code to generate error code to place into a proc
DECLARE @param_sql nvarchar(max)

SELECT @param_sql = '
--**Begin generated code to capture param values into a string**----------------
DECLARE @param_sql nvarchar(max)
DECLARE @param_output varchar(8000)
DECLARE @param_values varchar(8000)
SET @param_values = ''''

SELECT @param_sql = ISNULL(@param_sql + '', '') + '
SET @param_sql = ''SELECT @param_output = ISNULL(CAST(' + + ' AS varchar(8000)), ''''NULL'''')''
EXEC sp_executesql @param_sql, N''' + + ' ' + + CASE
    WHEN LIKE '%bin%' OR LIKE '%char%' THEN
        '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST(p.max_length AS varchar(10)) + ')' END
    WHEN IN ('datetime2', 'time') THEN
        '(' + CAST(p.scale AS varchar(3)) + ')'
    WHEN IN ('decimal', 'numeric') THEN
        '(' + CAST(p.precision AS varchar(3)) + ', ' + CAST(p.scale AS varchar(3)) + ')'
    WHEN IN ('float', 'real') THEN
        '(' + CAST(p.precision AS varchar(3)) + ')'
    ELSE '' END + ', @param_output varchar(8000) OUTPUT'', ' + + ', @param_output OUTPUT
SET @param_values = @param_values + '',' + + '='' + @param_output'
FROM sys.parameters p
INNER JOIN sys.types t ON
    t.system_type_id = p.system_type_id
    p.object_id = OBJECT_ID('dbo.test1')

SET @param_sql = @param_sql + '
SET @param_values = STUFF(@param_values, 1, 1, ''('') + '')''
--**End   generated code to capture param values into a string**----------------'

PRINT @param_sql

Sample use of error code gen'd above pasted into a test proc:

    @p1 int = 1,
    @p2 varchar(30) = NULL,
    @p3 datetime = 0
--EXEC dbo.test1

--**Begin generated code to capture param values into a string**----------------
DECLARE @param_sql nvarchar(max)
DECLARE @param_output varchar(8000)
DECLARE @param_values varchar(8000)
SET @param_values = ''

SET @param_sql = 'SELECT @param_output = ISNULL(CAST(@p1 AS varchar(8000)), ''NULL'')'
EXEC sp_executesql @param_sql, N'@p1 int, @param_output varchar(8000) OUTPUT', @p1, @param_output OUTPUT
SET @param_values = @param_values + ',@p1=' + @param_output
SET @param_sql = 'SELECT @param_output = ISNULL(CAST(@p2 AS varchar(8000)), ''NULL'')'
EXEC sp_executesql @param_sql, N'@p2 varchar(30), @param_output varchar(8000) OUTPUT', @p2, @param_output OUTPUT
SET @param_values = @param_values + ',@p2=' + @param_output
SET @param_sql = 'SELECT @param_output = ISNULL(CAST(@p3 AS varchar(8000)), ''NULL'')'
EXEC sp_executesql @param_sql, N'@p3 datetime, @param_output varchar(8000) OUTPUT', @p3, @param_output OUTPUT
SET @param_values = @param_values + ',@p3=' + @param_output
SET @param_values = STUFF(@param_values, 1, 1, '(') + ')'
--**End   generated code to capture param values into a string**----------------

--demo select to show the values contained in "@param_values" after code runs
SELECT @param_values AS param_values


EXEC dbo.test1
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

LVL 16

Expert Comment

by:Surendra Nath
ID: 39766183
wow.. scott that's really a good one...
LVL 69

Assisted Solution

ScottPletcher earned 500 total points
ID: 39766222

I was in a hurry so didn't make one change to make the code easier to use.

Add a variable to the very beginning of code, to hold the proc name to be processed:
DECLARE @proc_name sysname
SET @proc_name = 'dbo.test1'

And change the WHERE condition from this:
    p.object_id = OBJECT_ID('dbo.test1') --old code

to this:
    p.object_id = OBJECT_ID(@proc_name) --new code

Then the proc name can be entered at top of code instead of buried deep within code :-) .

Author Closing Comment

ID: 39768901
Great write up, I agree that this might be impractical but nice to have nevertheless... Thanks!
LVL 69

Expert Comment

ID: 39769232
Woah, I stated that doing it fully dynamically within every proc was impractical.  But not generating it and copying it into the proc, which to me is quite practical given what you want to do.

Expert Comment

ID: 41761421
What about grabbing the full statement of the call?  It has to be possible because SQL Profiler can do it.  So however SQL Profiler grabs it, can't we do that?  Wouldn't that fit the bill?  If someone knows how to get that, that would solve it for me too.

Everything I've tried just ends up grabbing the sp definition.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now