Is there a way to determine if a parameter in a stored proc has a default value (and thus not required) in code - .Net?

Question

I am already pulling the parameters from the stored proc sent in like this:

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

I looked into the SqlParameter object and could not find a way to see if it could tell me whether the Parameter had a default value... (although my debugger is acting weird, so who knows).

What I am doing is building a sort of Stored Proc helper for users.. I currently tell them all the parameters that belong to the Stored Proc they pick.... I WOULD REALLY like to be able to tell whether they are required.

Circumstantiate

Please add the sql-server tag. – Ampersand

Answer 1

To directly answer your question, no there is (probably) no way to determine if a stored procedure parameter possesses a default value 'in code' (i.e. using the SqlParameter class).

In SQL Server (at least SQL Server 2005), you can query the system catalog view sys.parameters (and join it to the catalog view sys.procedures) and evaluate the value of the column has_default_value.

Ampersand

Answer 2

Update to Devart's SQL - thank you so much, I needed to find what the default values are, not just if they had one, and your code got me started.

It did have a small bug: if a parameter name contained 'at' (like @d_at_e), the remaining parameters were not processed.

Using the variables speeds it up a lot, too.

Returns null if no default, otherwise returns everything between the '=' and the ',' in the declaration.

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

Waggish

Answer 3

Try this query. It returns default values for any stored procedure/function -

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

Hoffert

Answer 4

If you are looking at developing tools for management of SQL Server objects then a better option may be SMO. It looks like you can get to the default value of a stored procedure parameter using the StoredProcedureParameter Class.

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

Apply

Here's an example of code using this approach. I've verified that it does in fact get the default values for regular old non-CLR stored procs. stackoverflow.com/a/1676272 – Bunkum