sql-serverstored-proceduresparametersdefault-valuessms-2012

Get default value of stored procedure parameter


I have created below stored procedure with default value:

CREATE PROCEDURE [dbo].[Sample1] 
     @OrderID INT = 10285
AS
    SELECT ProductName, OrderID
    FROM Products P, [Order Details] Od
    WHERE Od.ProductID = P.ProductID 
      AND Od.OrderID = @OrderID

Tried to get default value (10285) of parameters using sys.parameters.

Select a.object_id, a.default_value
from sys.parameters a
inner join sys.types b on b.system_type_id = a.system_type_id
where Object_id = object_id('[dbo].[Sample1]')

But I got NULL as default_value, while I was expecting 10285 as default_value.

Is there any way to get default value?


Solution

  • It looks that Microsoft has neglected this topic and there is no trivial way to find parameters default values and even if a default value is present or not on a specific parameter:

    As we all know, T-SQL stored procedure parameter defaults are not stored in sys.parameters, all_parameters, and system_parameters. They are also not exposed through sp_sproc_columns, sys.columns, or sp_procedure_params_rowset.

    Feedback from Microsoft:

    As posted by Tibor Karaszi, BOL document that "SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function."

    We dont store even the bit that indicating parameter is of default value in Yukon.

    I have tested the first code snippet in this answer and it seems to work for your simple example:

    SELECT  
          data3.name
        , [default_value] = REVERSE(RTRIM(SUBSTRING(
              data3.rtoken
            , CASE 
                WHEN CHARINDEX(N',', data3.rtoken) > 0 
                    THEN CHARINDEX(N',', data3.rtoken) + 1
                WHEN CHARINDEX(N')', data3.rtoken) > 0 
                    THEN CHARINDEX(N')', data3.rtoken) + 1
                ELSE 1 
              END
            , LEN(data3.rtoken)
          )))
    FROM (
        SELECT  
              data2.name
            , rtoken = REVERSE(
                SUBSTRING(ptoken
                        , CHARINDEX('=', ptoken, 1) + 1
                        , LEN(data2.ptoken))
                    )
        FROM (
            SELECT  
                  data.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
                    , 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]
                        , tokens = REVERSE(SUBSTRING(sm2.tokens, ISNULL(CHARINDEX('SA', sm2.tokens) + 2, 0), LEN(sm2.tokens))) 
                    FROM (
                        SELECT 
                              sm.[object_id]
                            , o.[type]
                            , tokens = REVERSE(SUBSTRING(
                                          sm.[definition]
                                        , CHARINDEX(o.name, sm.[definition]) + LEN(o.name) + 1
                                        , ABS(CHARINDEX(N'AS', sm.[definition]))
                                     )  
                            ) 
                        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] = 'P '
                            AND s.name + '.' + o.name = 'dbo.Sample1'
                    ) 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
        WHERE data2.ptoken LIKE '%=%'
    ) data3
    

    However, it is really ugly for a task that one expects to be easily queryable from system views.