Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 parameter value question

Author  Topic 

Rekonn
Starting Member

7 Posts

Posted - 2014-04-03 : 12:10:36
I'm working on an application that uses stored procedures that have Try/Catch. If an error occurs, the name of the stored procedure and error data are saved to a table. I'd like to save parameter data too. I can code a different insert statement for each stored procedure, but was wondering if there's a way to do it more programmatically, so I can reuse the same code.

Within the catch block of a SP, I know I can run the below to get the name of the first parameter.

select parameter_name
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = (OBJECT_NAME(@@PROCID))
and ORDINAL_POSITION = 1

But, I'm stuck on how to get the value of it. Any ideas?

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-03 : 15:25:40
sql server won't capture and save any parameter values. You might have to write the manual insert statement to the audit table to do so.


Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-03 : 16:00:53
That view should contain all parameters.

I suggest you use sys.parameters instead. The I_S views often perform very poorly compared to the native SQL sys. views.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-03 : 16:14:08
For capturing the values, you'll need dynamic SQL.

I dynamically generate static code to capture the parameter values in the error-handling code in the proc. That makes the process less error-prone and reduces run-time overhead.

Below is the code that generates code to capture all parameter values for the proc name you set at the top of the code.


DECLARE @proc_name sysname
DECLARE @param_sql nvarchar(max)
SET @proc_name = 'dbo.your_proc_name_goes_here'

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(@proc_name)
ORDER BY
p.parameter_id

SET @param_sql = @param_sql + '
SET @param_values = STUFF(@param_values, 1, 1, ''('') + '')''
--SELECT @param_values AS param_values
--**End generated code to capture param values into a string**----------------'

PRINT @param_sql

Go to Top of Page

Rekonn
Starting Member

7 Posts

Posted - 2014-04-03 : 17:15:32
Scott, thanks!! I think I'm close, working through some errors. I'm trying to do this with my test SP:
takes one parameter, @C char(1)

BEGIN TRY
select 1/0
END TRY
BEGIN CATCH
your code copy pasted, seeing if this will give value of @C
END CATCH
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-04 : 10:26:33
Remember, the code I posted does not go in the proc. The code generated by the code I posted goes in the proc.
Go to Top of Page
   

- Advertisement -