Click here to Skip to main content
15,881,709 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello

i have created on store procedure in which i want to trace my parameters with value on fire error

for Example,

create procedure [dbo].[test_store_procedure]
	@Start_dt DATETIME = getdate(),
	@End_dt DATETIME = getdate() + 12,
	@Show_Renewed BIT = 1,
	@Show_NonRenewable BIT = 0,
	@Show_Claims BIT = 1,
	@Error_Code INT = 0
as
begin
begin try
DECLARE @Params VARCHAR(max) = 'select '

SELECT @Params += ''' ' + p.NAME + ' = '' +  cast(' + p.NAME + ' as varchar) + ' + ''' , ''' + '+'
FROM sys.parameters p
INNER JOIN sys.types sty ON sty.system_type_id = p.system_type_id 
	AND sty.NAME NOT IN ('varbinary') AND sty.max_length != 8000
WHERE OBJECT_ID = OBJECT_ID('dbo.test_store_procedure')
ORDER BY p.parameter_id
SET @Params = LEFT(@Params, LEN(@Params) - 9)
PRINT @Params
EXEC (@Params)

--some statements

end try
begin catch
	insert into adm_trace_para (sp_name,parameters) values (test_store_procedure,@Params)
end catch
end


But here i can not use declared parameters in dynamic query.
how can we accomplish to trace parameters with values

SQL
In my store procedure when any error occurs then i want to insert a parameters with value in one table named "adm_trace_para".

i mean i dont want to user sql trace to find store procedure parameters i need it in my sql table.




Thanks
Posted
Updated 26-Nov-14 0:20am
v2
Comments
Maciej Los 26-Nov-14 5:53am    
Sorry, not clear...
Shweta N Mishra 26-Nov-14 6:18am    
Declared parameters you mean the variable declared inside sp ?

Or you only want to trace the parameter values passed to the SP while executing it ?
PKriyshnA 26-Nov-14 6:21am    
Yes,
i only want to trace the parameter values passed to the SP while executing it.
without using sql trace.

i can insert it manually but i want to trace for all store procedure.

You can store them as
SQL
insert into adm_trace_para (sp_name,parameters) values (test_store_procedure,'@Start_dt='+@Start_dt+':@End_dt='+@End_dt+':@Show_Renewed='+@Show_Renewed+':@Show_NonRenewable='+@Show_NonRenewable+':@Show_Claims='+@Show_Claims+':@Error_Code='+@Error_Code)
 
Share this answer
 
v2
Comments
PKriyshnA 26-Nov-14 6:30am    
Thats absulately right but i don't want to write for many no of store procedure.

i can get a parameter name by

SELECT p.NAME
FROM sys.parameters p
INNER JOIN sys.types sty ON sty.system_type_id = p.system_type_id
AND sty.NAME NOT IN ('varbinary') AND sty.max_length != 8000
WHERE OBJECT_ID = OBJECT_ID('dbo.test_store_procedure')
ORDER BY p.parameter_id

by using this sql how can i trace parameters with value.
some of store procedure may have a 100s no of parameters so need to get dynamically.
Shweta N Mishra 26-Nov-14 6:56am    
I understand what you are saying, but you could not do this in SQL

check this

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/170c6fe1-3392-4a66-bc13-37a59f1d5967/how-to-get-all-parameter-names-along-with-their-values-in-stored-procedure-which-is-being-executed?forum=transactsql

But you can do this in your UI coding.
How to: Debug Stored Procedures[^] could provide some help to you.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900