Question

我曾搜索过,但无法找到如何从2008年服务器的实验/渔获量科获取存储程序参数和价值。

我知道如何获得参数,例如:

SELECT  parm.name AS Parameter
        ,typ.name AS [Type]
FROM    sys.procedures sp
        JOIN sys.parameters parm ON sp.object_id = parm.object_id
        JOIN sys.types typ ON parm.system_type_id = typ.system_type_id
WHERE   sp.name = OBJECT_NAME(@@procid)

基本上,我所期待的是具有参数和价值观的SP,以便我能够记录和解决麻烦。类似:

wsp_GetUser_s @UserID = 123, @UserName =  juser

Answer 1

微软KS服务器 副渔获物/strong>栏中没有任何特征可自动记录输入数据库物体的参数。

如果你想要知道哪些参数已经通过,我建议追踪或利用扩展的活动。

If neither of those options are feasible I recommend creating a table to store this information. One column can have the Stored Procedure name and another column can have the parameters. The parameters column can contain a comma delimited list of whatever parameters were passed into the stored procedure.

例:

create table dbo.Parameters ( RowID int identity(1,1), StoredProcedure varchar(255), Parameters varchar(max) ) create proc dbo.wsp_GetUser_s ( @UserID int, @UserName varchar(255) ) as begin try select * from dbo.Users where UserID = @UserID and UserName = @UserName -- We ll only log to the Parameters table if we don t find a match if @@rowcount < 1 begin RAISERROR ( Did not find user. Logging passed parameters to dbo.Parameters table , -- Message text. 16, -- Severity. 1 -- State. ); end end try begin catch -- This could be moved into the catch block if you wanted to log this for all calls and not only when there is no match found for the passed in parameters. insert into dbo.Parameters ( StoredProcedure, Parameters) select wsp_GetUser_s , @UserID= + @UserID + , @UserName= + @UserName end catch

友情链接