Solved

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

Posted on 2014-01-07
10
291 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 51
MS SQL Backup 24 70
Distinct values from two tables 14 20
while loop in html mail format 5 33
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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

23 Experts available now in Live!

Get 1:1 Help Now