• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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

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:
    p.name AS Parameter,        
    t.name 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 sp.name = '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

  • 3
  • 3
  • 2
  • +2
2 Solutions
Surendra NathTechnology LeadCommented:
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.
Anthony PerkinsCommented:
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?
chrisjmccrumAuthor Commented:
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...
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Surendra NathTechnology LeadCommented:
At this moment there is no such functionality with SQL Server, nor it is planned for future releases.
Scott PletcherSenior DBACommented:
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(' + p.name + ' AS varchar(8000)), ''''NULL'''')''
EXEC sp_executesql @param_sql, N''' + p.name + ' ' + t.name + CASE
    WHEN t.name LIKE '%bin%' OR t.name LIKE '%char%' THEN
        '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST(p.max_length AS varchar(10)) + ')' END
    WHEN t.name IN ('datetime2', 'time') THEN
        '(' + CAST(p.scale AS varchar(3)) + ')'
    WHEN t.name IN ('decimal', 'numeric') THEN
        '(' + CAST(p.precision AS varchar(3)) + ', ' + CAST(p.scale AS varchar(3)) + ')'
    WHEN t.name IN ('float', 'real') THEN
        '(' + CAST(p.precision AS varchar(3)) + ')'
    ELSE '' END + ', @param_output varchar(8000) OUTPUT'', ' + p.name + ', @param_output OUTPUT
SET @param_values = @param_values + '',' + p.name + '='' + @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
Surendra NathTechnology LeadCommented:
wow.. scott that's really a good one...
Scott PletcherSenior DBACommented:

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 :-) .
chrisjmccrumAuthor Commented:
Great write up, I agree that this might be impractical but nice to have nevertheless... Thanks!
Scott PletcherSenior DBACommented:
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now