如何在sql中跟踪Store Procudure参数 [英] How to trace a Store Procudure Parameters in sql

问题描述

你好



i已经在商店程序中创建了我想用火灾错误值追踪我的参数


例如
，

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

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

推荐答案

您可以将它们存储为

You can store them as

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)

如何：调试存储过程 [ ^ ]可以提供一些帮助你。

How to: Debug Stored Procedures[^] could provide some help to you.

这篇关于如何在sql中跟踪Store Procudure参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！