chrisjmccrum
asked on
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:
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',@orde rpurchasen umber='123 45',@order anything=' something' ,etc)
Here is our current sql catch statement:
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'
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',@orde
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
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?
ASKER
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...
Maybe I'm asking too much from SQL haha...
At this moment there is no such functionality with SQL Server, nor it is planned for future releases.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
wow.. scott that's really a good one...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great write up, I agree that this might be impractical but nice to have nevertheless... Thanks!
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.
Everything I've tried just ends up grabbing the sp definition.
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.