Solved

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

Posted on 2014-01-07
10
293 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
  • 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
 
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:
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(' + 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
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...
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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:ScottPletcher
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

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