Solved

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

Posted on 2014-01-07
10
296 Views
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 
    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:
	BEGIN CATCH 
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;
		DECLARE @ObjectName NVARCHAR(100);

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

Open in new window

0
Comment
Question by:chrisjmccrum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
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.
0
 
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?
0
 

Author Comment

by:chrisjmccrum
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...
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher 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(' + 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
WHERE
    p.object_id = OBJECT_ID('dbo.test1')
ORDER BY
    p.parameter_id

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:


CREATE PROCEDURE dbo.test1
    @p1 int = 1,
    @p2 varchar(30) = NULL,
    @p3 datetime = 0
AS
--EXEC dbo.test1
SET NOCOUNT ON

--**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

GO

EXEC dbo.test1
0
 
LVL 16

Expert Comment

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

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 39766222
Thanks!

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:
WHERE
    p.object_id = OBJECT_ID('dbo.test1') --old code

to this:
WHERE
    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 :-) .
0
 

Author Closing Comment

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

Expert Comment

by:Scott Pletcher
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.
0
 

Expert Comment

by:Kakoroat
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.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

733 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