存储过程默认参数标识

问题描述：

我有一个服务于Web服务调用的过程。这是一个SQL 2012环境。 proc采用存储过程名称和任意一组过程参数（用字符串分隔符分隔）。在proc内部我建立如下陈述： -存储过程默认参数标识

INSERT INTO @Params 
    SELECT p.parameter_id, p.name AS ParameterName, t.name AS ParameterType, p.max_length AS ParameterLength, '' 
    FROM sys.parameters AS p 
    JOIN sys.types AS t ON t.user_type_id = p.user_type_id 
    WHERE object_id = OBJECT_ID(RTRIM(LTRIM(@ProcName))) 

    UPDATE P 
    SET pValue = s.data 
    FROM dbo.split(@ParamList,'ç') as S 
    INNER JOIN @Params as P ON P.id = S.ID 


    SELECT @ParamSplit = ISNULL(Stuff((SELECT ',' + 
     pName + '=' + 
     CASE 
      WHEN pValue = 'ÆNULL' THEN 'NULL' 
      WHEN pType = 'varchar' THEN char(39) + replace(pValue,char(39),char(39) + char(39)) + char(39) 
      WHEN pType = 'date' THEN char(39) + replace(pValue,char(39),char(39) + char(39)) + char(39) 
      WHEN pType = 'datetime' or pType='TIME' THEN char(39) + replace(pValue,char(39),char(39) + char(39)) + char(39) 
      else pValue 
      END 
    FROM @Params 
    FOR XML PATH ('')),1,1,''),'')

这工作正常。我明白并非所有的数据类型都被检查过，但为了我的目的（这是一个封闭的系统），它涵盖了所有必需的基础。但我有一个问题，因为调用服务必须传递所有参数，甚至可能在proc中默认的参数。

有一个在sys.parameters表是

has_default_value

和另一场

DEFAULT_VALUE

一个领域，但检查微软记录他们说： -

默认值记录在sys.parameters.default列只 CLR过程。对于Transact-SQL 过程参数，该列将为NULL。

我想要做的是确定如果我调用的proc具有任何默认参数，如果他们这样做（并且调用服务没有通过它们）我可以使用默认值，否则这些可以被替换为无论呼叫服务已通过。

所以...问题 - 有谁知道如何识别proc是否有任何默认参数分配？

到的答案[此问题]（http://*.com/q/5873731/1048425）包含解析过程定义以检索默认值的方法。我不确定是否有更干净的做法。 – GarethD

你可以：1）创建将拦截CREATE/ALTER过程的DDL触发器2）解析定义以检索默认值3）放入扩展属性4）在你的代码列表中从sys.parameters中获取所有参数并获取默认值来自扩展事件。像** [demo]（http://rextester.com/GGXWTY28224）**。完全可以以自动化的方式实现它。如果您在'JSON/XML格式'中有某种文档作为注释，则解析可能更简单。 – lad2025

这是一个现有的生产数据库，因此不太实际的打开引擎盖。我可以查询有什么 - 不能真正修改任何特效。我认为在运行时解析会效率低下（这些过程称为很多，每秒可能多次）。我以为我能做的就是假设如果Web服务没有通过所有的参数，那么任何错过的必须由defaut - 所以proc可以被调用没有这些参数。我认为这将起作用... –

答

你可以试试这个，但可能需要检查您的存储过程defintions的格式，你可以从你的应用程序和缓存在客户端调用这个防止性能开销：

Declare @ProcedureName VARCHAR(50) = 'MySP' 
DECLARE @text VARCHAR(MAX) 
DECLARE @startPos int = 0 
DECLARE @endPos int = 0 

SELECT @text=text FROM syscomments 
WHERE id = object_id(@ProcedureName) and colid=1 AND text LIKE '%(%' 

IF @text IS NULL 
BEGIN 
    -- No Parameters in stored procedure 
    PRINT 'No Parameters' 
END 
ELSE 
BEGIN 

    -- Find Start of parameter declaration 
    SELECT @startPos = PATINDEX('%(%' ,@text) 
    -- Find end of parameter declaration 
    SELECT @endPos = PATINDEX('%AS' + CHAR(13) + '%' ,@text) 

    -- Set Text to our parameters string 
    SELECT @text = SUBSTRING(@text, @startPos + 1, @endpos - @startpos -4) 

    -- Split parameters and insert into table 
    -- Alternatively use other string splitting techniques such as numbers table 
    DECLARE @t1 TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)) 
    DECLARE @xml XML 
    SET @xml = N'<t>' + REPLACE(@text,',','</t><t>') + '</t>' 
    INSERT INTO @t1(val) 
    SELECT r.value('.','varchar(MAX)') as item 
    FROM @xml.nodes('/t') as records(r) 


    SELECT SUBSTRING(val, PATINDEX('%@%', val), PATINDEX('% %', val) - PATINDEX('%@%', val)) as Parm, 
      SUBSTRING(val, PATINDEX('%=%',val) + 1, DATALENGTH(val) - PATINDEX('%=%',val)) As DefaultValue 
    FROM @t1 
    WHERE val like '%=%' -- Only get parameters with default value 

END

存储过程默认参数标识

相关推荐