有没有办法确定存储过程中的参数在代码 - .Net 中是否具有默认值（因此不需要）？

Question

我已经从像这样发送的存储过程中提取参数：

 foreach (SqlParameter param in cmd.Parameters)
            {
               if ((param.Direction == ParameterDirection.Input) || (param.Direction == ParameterDirection.InputOutput))
                {
                    jsonReturn += "{\"paramName\":\"" + param.ParameterName + "\", \"paramType\":\"" + param.SqlDbType.ToString() + "\"},";
                }
            }

我查看了 SqlParameter 对象，但找不到一种方法来查看它是否可以告诉我参数是否有默认值...（尽管我的调试器正在运行很奇怪，所以谁知道）。

我正在做的是为用户构建一种存储过程助手。我目前告诉他们属于他们选择的存储过程的所有参数。我真的很希望能够知道它们是否是必需的。

Answer 1

更新 Devart 的 SQL - 非常感谢你，我需要找到默认值是什么，而不仅仅是他们是否有默认值，你的代码让我开始了。

它确实有一个小错误：如果参数名称包含“at”（如@d_at_e），则其余参数不会被处理。

使用变量也可以大大加快速度。

如果没有默认值，则返回 null，否则返回声明中“=”和“,”之间的所有内容。

declare @ProcName nvarchar(200) = 'your_stored_proc_name'
declare @objectid int
declare @type nchar(2)
declare @oName nvarchar(100)
declare @sName nvarchar(100)
declare @Definition nvarchar(max)


select  @objectid = o.[object_id], 
        @type = o.type, 
        @oName = o.name, 
        @sName = s.name, 
        @Definition = replace(replace(sm.[definition], char(10),' '), char(13),' ')
                    from sys.sql_modules sm WITH (NOLOCK)
                    JOIN sys.objects o WITH (NOLOCK) ON sm.[object_id] = o.[object_id] 
                    JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
                    WHERE o.[type] IN ('P ', 'FN', 'IF', 'TF')
                        AND s.name + '.' + o.name =  @ProcName 

    SELECT
          data2.[object_name] as StoredProcedure
        , data2.name as ParameterName
        , DefaultValue = 
            CASE WHEN data2.ptoken LIKE '%=%' 
                THEN SUBSTRING(data2.ptoken, CHARINDEX('=', data2.ptoken)+1, CHARINDEX(',',data2.ptoken+',',CHARINDEX('=', data2.ptoken))-CHARINDEX('=', data2.ptoken)-1)
                ELSE null
            END
    FROM (
        SELECT  
              data.name
            , data.[object_name]
            , ptoken = SUBSTRING(
                    data.tokens
                , token_pos + name_length + 1
                , ISNULL(ABS(next_token_pos - token_pos - name_length - 1), LEN(data.tokens))
            )
        FROM (
            SELECT  
                  sm3.tokens
                , sm3.[object_name]
                , p.name
                , name_length = LEN(p.name)
                , token_pos = CHARINDEX(p.name, sm3.tokens)
                , next_token_pos = CHARINDEX(p2.name, sm3.tokens)
            FROM (
                SELECT 
                      sm2.[object_id]
                    , sm2.[type]
                    , sm2.[object_name]
                    , tokens = REVERSE(
                        CASE WHEN sm2.[type] IN ('FN', 'TF', 'IF') 
                            THEN SUBSTRING(sm2.tokens, ISNULL(CHARINDEX(N')', sm2.tokens) + 1, 0), LEN(sm2.tokens)) 
                            ELSE SUBSTRING(sm2.tokens, ISNULL(CHARINDEX(' SA ', sm2.tokens) + 2, 0), LEN(sm2.tokens))  
                        END
                    ) 
                FROM (
                    SELECT 
                          @objectid as [object_id]
                        , @type as [type]
                        , @sName + '.' + @oName as [object_name] 
                        , tokens = REVERSE(CASE WHEN @type IN ('FN', 'TF', 'IF') 
                            THEN SUBSTRING(
                                      @Definition
                                    , CHARINDEX(N'(', @Definition) + 1
                                    , ABS(CHARINDEX(N'RETURNS', @Definition) - CHARINDEX(N'(', @Definition) - 1)
                                    ) 
                            ELSE SUBSTRING(
                                      @Definition
                                    , CHARINDEX(@oName, @Definition) + LEN(@oName) + 1
                                    , ABS(CHARINDEX(N' AS ', @Definition) - (CHARINDEX(@oName, @Definition) + LEN(@oname) + 1))
                                    )  
                            END
                            )
                ) sm2
                WHERE sm2.tokens LIKE '%=%'
            ) sm3
            JOIN sys.parameters p WITH (NOLOCK) ON sm3.[object_id] = p.[object_id]
            OUTER APPLY (
                SELECT p2.name
                FROM sys.parameters p2 WITH (NOLOCK) 
                WHERE p2.is_output = 0
                    AND sm3.[object_id] = p2.[object_id] 
                    AND p.parameter_id + 1 = p2.parameter_id
            ) p2
            WHERE p.is_output = 0
        ) data
    ) data2

Answer 2

为了直接回答您的问题，不（可能）没有办法确定存储过程参数是否拥有“代码中”的默认值（即使用 SqlParameter 类）。

在 SQL Server（至少 SQL Server 2005）中，您可以查询系统目录视图 sys.parameters（并将其连接到目录视图 sys.procedures）并评估has_default_value 列的值。

Answer 3

尝试这个查询。它返回任何存储过程/函数的默认值 -

SELECT
      data2.[object_name]   
    , data2.name
    , has_default_value = 
        CASE WHEN data2.ptoken LIKE '%=%' 
            THEN 1 
            ELSE 0
        END
FROM (
    SELECT  
          data.name
        , data.[object_name]
        , ptoken = SUBSTRING(
                data.tokens
            , token_pos + name_length + 1
            , ISNULL(ABS(next_token_pos - token_pos - name_length - 1), LEN(data.tokens))
        )
    FROM (
        SELECT  
              sm3.tokens
            , sm3.[object_name]
            , p.name
            , name_length = LEN(p.name)
            , token_pos = CHARINDEX(p.name, sm3.tokens)
            , next_token_pos = CHARINDEX(p2.name, sm3.tokens)
        FROM (
            SELECT 
                  sm2.[object_id]
                , sm2.[type]
                , sm2.[object_name]
                , tokens = REVERSE(
                    CASE WHEN sm2.[type] IN ('FN', 'TF', 'IF') 
                        THEN SUBSTRING(sm2.tokens, ISNULL(CHARINDEX(N')', sm2.tokens) + 1, 0), LEN(sm2.tokens)) 
                        ELSE SUBSTRING(sm2.tokens, ISNULL(CHARINDEX('SA', sm2.tokens) + 2, 0), LEN(sm2.tokens))  
                    END
                ) 
            FROM (
                SELECT 
                      sm.[object_id]
                    , o.[type]
                    , [object_name] = s.name + '.' + o.name
                    , tokens = REVERSE(CASE WHEN o.[type] IN ('FN', 'TF', 'IF') 
                        THEN SUBSTRING(
                                  sm.[definition]
                                , CHARINDEX(N'(', sm.[definition]) + 1
                                , ABS(CHARINDEX(N'RETURNS', sm.[definition]) - CHARINDEX(N'(', sm.[definition]) - 1)
                                ) 
                        ELSE SUBSTRING(
                                  sm.[definition]
                                , CHARINDEX(o.name, sm.[definition]) + LEN(o.name) + 1
                                , ABS(CHARINDEX(N'AS', sm.[definition]))
                                )  
                        END
                    ) 
                FROM sys.sql_modules sm WITH (NOLOCK)
                JOIN sys.objects o WITH (NOLOCK) ON sm.[object_id] = o.[object_id] 
                JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
                WHERE o.[type] IN ('P ', 'FN', 'IF', 'TF')
                    AND s.name + '.' + o.name = 'dbo.usp_test1'
            ) sm2
            WHERE sm2.tokens LIKE '%=%'
        ) sm3
        JOIN sys.parameters p WITH (NOLOCK) ON sm3.[object_id] = p.[object_id]
        OUTER APPLY (
            SELECT p2.name
            FROM sys.parameters p2 WITH (NOLOCK) 
            WHERE p2.is_output = 0
                AND sm3.[object_id] = p2.[object_id] 
                AND p.parameter_id + 1 = p2.parameter_id
        ) p2
        WHERE p.is_output = 0
    ) data
) data2

Answer 4

如果您正在寻找用于管理 SQL Server 对象的开发工具，那么更好的选择可能是 SMO。看起来您可以使用 StoredProcedureParameter 类。

http: //msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.parameter.defaultvalue(v=SQL.100).aspx