none
How can i find the default values for a Stored Procedure parameter

    Question

  • I have a stored procedure,

    CREATE PROCEDURE [dbo].[SReport]

    (

    @ListByBatch BIT,

    @UnPrintedOnly BIT = 1

    )

    AS

    ............................

    ........

    I can find the information regarding parameters of this Stored Procedure from "syscolumns" or 

    "Information_Schema.Parameters" system tables. These tables provides information about parametername, datatype ...etc. But dont provide information about default values for these parameters.

    Here i have assigned a value, Does SQL Server stores this value????

    Does SQL Server maintains information about Default Values for Parameter ? (For tables it maintains the default value for a column).

    Is there any way , i can find All parameter information for a stored procedure including Default value..........????

    Sunday, February 18, 2007 6:15 AM

Answers

  • Hi,
    you can try
    sp_procedure_params_rowset

    sp_procedure_params_rowset will return the following result set.
    PROCEDURE_CATALOG Name of the database containing the procedure.
    PROCEDURE_SCHEMA Owner's name of the procedure.
    PROCEDURE_NAME Name of the procedure.
    PARAMETER_NAMEName of the parameter.
    ORDINAL_POSITION Ordinal position of the parameter.
    PARAMETER_TYPE If the parameter is an OUTPUT parameter or not.
    PARAMETER_HASDEFAULTIf the parameter has a default value.
    PARAMETER_DEFAULTThe default value of the parameter.
    IS_NULLABLEIf the parameter accepts NULL values.
    DATA_TYPEData type of the parameter.
    CHARACTER_MAXIMUM_LENGTHThe maximum length of the parameter's data.
    CHARACTER_OCTET_LENGTHThe maximum octect length of the parameter's data.
    NUMERIC_PRECISIONThe precision of the parameter.
    NUMERIC_SCALEThe scale of the parameter.
    DESCRIPTIONDescription of the parameter. Always NULL.
    TYPE_NAMEActual name for the data type.
    LOCAL_TYPE_NAMELocal name for the data type, if the data type is a user-defined data type.

    Sunday, February 18, 2007 8:25 AM
  • As indicated above, the following system procedure will do the job:

    exec sp_procedure_params_rowset uspGetBillOfMaterials
    

    PROCEDURE_CATALOG PROCEDURE_SCHEMA PROCEDURE_NAME PARAMETER_NAME ORDINAL_POSITION PARAMETER_TYPE PARAMETER_HASDEFAULT PARAMETER_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DESCRIPTION TYPE_NAME LOCAL_TYPE_NAME
    AdventureWorks2008 dbo uspGetBillOfMaterials;1 @RETURN_VALUE 0 4 0 NULL 0 3 NULL NULL 10 NULL NULL int int
    AdventureWorks2008 dbo uspGetBillOfMaterials;1 @StartProductID 1 1 0 NULL 1 3 NULL NULL 10 NULL NULL int int
    AdventureWorks2008 dbo uspGetBillOfMaterials;1 @CheckDate 2 1 0 NULL 1 135 NULL NULL NULL NULL NULL datetime datetime


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Monday, September 26, 2011 12:19 AM

All replies

  • Hi,
    you can try
    sp_procedure_params_rowset

    sp_procedure_params_rowset will return the following result set.
    PROCEDURE_CATALOG Name of the database containing the procedure.
    PROCEDURE_SCHEMA Owner's name of the procedure.
    PROCEDURE_NAME Name of the procedure.
    PARAMETER_NAMEName of the parameter.
    ORDINAL_POSITION Ordinal position of the parameter.
    PARAMETER_TYPE If the parameter is an OUTPUT parameter or not.
    PARAMETER_HASDEFAULTIf the parameter has a default value.
    PARAMETER_DEFAULTThe default value of the parameter.
    IS_NULLABLEIf the parameter accepts NULL values.
    DATA_TYPEData type of the parameter.
    CHARACTER_MAXIMUM_LENGTHThe maximum length of the parameter's data.
    CHARACTER_OCTET_LENGTHThe maximum octect length of the parameter's data.
    NUMERIC_PRECISIONThe precision of the parameter.
    NUMERIC_SCALEThe scale of the parameter.
    DESCRIPTIONDescription of the parameter. Always NULL.
    TYPE_NAMEActual name for the data type.
    LOCAL_TYPE_NAMELocal name for the data type, if the data type is a user-defined data type.

    Sunday, February 18, 2007 8:25 AM
  • Hi,

    Thanks for reply.

    I tried this , But it shows NULL for PARAMETER_DEFAULT and 0 for PARAMETER_HASDEFAULT though i have set default value for that parameter.

    Is there any other way , we can find this default value?

     

    Monday, February 19, 2007 6:29 AM
  • SQL SERVER does not store the default parameter values for transact sql stored procedure. It does so for clr stored procedures. See here

    http://msdn2.microsoft.com/en-us/library/ms176074.aspx

    In SQL SERVER 2005, You can use object_definition() function to get the definition of the procedure and then parse it to get the default parameter values.

    In SQL 2K, the syscomments system table stores the definitions for the various stored procedures.

    Monday, February 19, 2007 9:29 PM
  • Right, you won't be able to find these.  SQL Server parses a Stored Procedure everytime it runs it (no matter if it has an Execution Plan or not).  This allows for the Default values to take effect.  However, you'll never see these in any MetaData within SQL because it would take a lot of RegEx and parsing to actually determine what the Default values are, not to mention depending on the data type things could get really weird.  So that's why they don't do it.
    Wednesday, August 20, 2008 7:55 PM
  • This is what I did to get it. grab the section of the stored procedure starting with the first parameter up to the AS statement. Created a temporary stored procedure with the declare statements and returning union all of all the parameter ids, names, column types, if they have default, and their value. And then executed the stored procedure with assumption of if there is a equal sign between parameters they have default, and if they don't have default I passed null to the parameter during execution, and either read the resultset or if exists the stored procedure populated a temporary table so that I can query it later. I checked if there is any equal signs between parameters and if yes initially I assumed they have defaults. If there is a comment etc with equal sign the procedure that means they did not have default and during execution I did not pass any parameter, the execution failed, I caught the error message, read the parameter name and executed the procedure this time I passed null to the parameter. In the procedure I used a CLR string concat function, for that reason it won't compile if you execute directly, but you can probably replace with XML path or so, or email me back I can guide you through the clr if you want to. Since I did union all the parameters I casted them as varchar(max)

     

    USE Util

    GO

    CREATE AGGREGATE [dbo].[StringConcat]

    (@Value nvarchar(MAX), @Delimiter nvarchar(100))

    RETURNS nvarchar(MAX)

    EXTERNAL NAME [UtilClr].[UtilClr.Concat]

    GO

    CREATE FUNCTION dbo.GetColumnType (@TypeName SYSNAME,

                                      @MaxLength SMALLINT,

                                      @Precision TINYINT,

                                      @Scale TINYINT,

                                      @Collation SYSNAME,

                                      @DBCollation SYSNAME)

    RETURNS TABLE

        AS

    RETURN

        SELECT  CAST(CASE WHEN @TypeName IN ('char', 'varchar')

                          THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'

                                                      ELSE CAST(@MaxLength AS VARCHAR)

                                                 END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation

                                                                  ELSE ''

                                                             END

                          WHEN @TypeName IN ('nchar', 'nvarchar')

                          THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'

                                                      ELSE CAST(@MaxLength / 2 AS VARCHAR)

                                                 END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation

                                                                  ELSE ''

                                                             END

                          WHEN @TypeName IN ('binary', 'varbinary') THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'

                                                                                                ELSE CAST(@MaxLength AS VARCHAR)

                                                                                           END + ')'

                          WHEN @TypeName IN ('bigint', 'int', 'smallint', 'tinyint') THEN @TypeName

                          WHEN @TypeName IN ('datetime2', 'time', 'datetimeoffset') THEN @TypeName + '(' + CAST (@Scale AS VARCHAR) + ')'

                          WHEN @TypeName IN ('numeric', 'decimal') THEN @TypeName + '(' + CAST(@Precision AS VARCHAR) + ', ' + CAST(@Scale AS VARCHAR) + ')'

                          ELSE @TypeName

                     END AS VARCHAR(256)) AS ColumnType

    GO

    go

    USE [master]

    GO

    IF OBJECT_ID('dbo.sp_ParamDefault') IS NULL 

        EXEC('CREATE PROCEDURE dbo.sp_ParamDefault AS SELECT 1 AS ID')

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE dbo.sp_ParamDefault

        @ProcName SYSNAME = NULL OUTPUT

    AS 

    SET NOCOUNT ON

    SET ANSI_WARNINGS OFF

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

     

    DECLARE @SQL VARCHAR(MAX),

        @ObjectId INT = OBJECT_ID(LTRIM(RTRIM(@ProcName))),

        @FirstParam VARCHAR(256),

        @LastParam VARCHAR(256),

        @SelValues VARCHAR(MAX),

        @ExecString VARCHAR(MAX),

        @WhiteSpace VARCHAR(10) = '[' + CHAR(10) + CHAR(13) + CHAR(9) + CHAR(32) + ']',

        @TableExists BIT = ABS(SIGN(ISNULL(OBJECT_ID('tempdb..#sp_ParamDefault'), 0))),

        @DeclareSQL VARCHAR(MAX),

        @ErrorId INT,

        @ErrorStr VARCHAR(MAX)

     

    IF @ObjectId IS NULL 

        BEGIN

            SET @ProcName = NULL

            PRINT '/* -- SILENCE OPERATION --

    IF OBJECT_ID(''tempdb..#sp_ParamDefault'') IS NOT NULL DROP TABLE #sp_ParamDefault

    CREATE TABLE #sp_ParamDefault (Id INT, NAME VARCHAR(256), TYPE VARCHAR(256), HasDefault BIT, IsOutput BIT, VALUE VARCHAR(MAX))

    */

     

    EXEC dbo.sp_ParamDefault

        @ProcName = NULL

    '

    RETURN

        END

     

    SELECT  @SQL = definition,

            @ProcName = QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectId)) + '.' + QUOTENAME(OBJECT_NAME(@ObjectId)),

            @FirstParam = FirstParam,

            @LastParam = LastParam

    FROM    sys.all_sql_modules m (NOLOCK)

    CROSS APPLY (SELECT MAX(CASE WHEN p.parameter_id = 1 THEN p.name

                            END) AS FirstParam,

                        Util.dbo.StringConcat(p.name, '%') AS Params

                 FROM   sys.parameters p (NOLOCK)

                 WHERE  p.object_id = m.OBJECT_ID) p

    CROSS APPLY (SELECT TOP 1

                        p.NAME AS LastParam

                 FROM   sys.parameters p (NOLOCK)

                 WHERE  p.object_id = m.OBJECT_ID

                 ORDER BY parameter_id DESC) l

    WHERE   m.object_id = @ObjectId

    IF @FirstParam IS NULL 

        BEGIN

            IF @TableExists = 0 

                SELECT  CAST(NULL AS INT) AS Id,

                        CAST(NULL AS VARCHAR(256)) AS Name,

                        CAST(NULL AS VARCHAR(256)) AS Type,

                        CAST(NULL AS BIT) AS HasDefault,

                        CAST(NULL AS VARCHAR(MAX)) AS VALUE

                WHERE   1 = 2

            RETURN

        END

     

    SELECT  @DeclareSQL = SUBSTRING(@SQL, 1, lst + AsFnd + 2) + '

    '

    FROM    (SELECT PATINDEX ('%' + @WhiteSpace + @LastParam + @WhiteSpace + '%', @SQL) AS Lst) l

    CROSS APPLY (SELECT SUBSTRING (@SQL, lst, LEN (@SQL)) AS SQL2) s2

    CROSS APPLY (SELECT PATINDEX ('%' + @WhiteSpace + 'AS' + @WhiteSpace + '%', SQL2)  AS AsFnd) af

     

     

    DECLARE @ParamTable TABLE (Id INT NOT NULL,

                               NAME SYSNAME NULL,

                               TYPE VARCHAR(256) NULL,

                               HasDefault BIGINT NULL,

                               IsOutput BIT NOT NULL,

                               TypeName SYSNAME NOT NULL) ;

    WITH    pr

              AS (SELECT    p.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS ParameterName,

                            p.Parameter_id,

                            t.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeName,

                            ct.ColumnType,

                            MAX(Parameter_id) OVER (PARTITION BY (SELECT 0)) AS MaxParam,

                            p.is_output

                  FROM      sys.parameters p (NOLOCK)

                  INNER JOIN sys.types t (NOLOCK) ON t.user_type_id = p.user_type_id

                  INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()

                  CROSS APPLY Util.dbo.GetColumnType(t.name, p.max_length, p.precision, p.scale, db.collation_name, db.collation_name) ct

                  WHERE     OBJECT_ID = @ObjectId)

        INSERT  @ParamTable

                (Id,

                 NAME,

                 TYPE,

                 HasDefault,

                 IsOutput,

                 TypeName)

                SELECT  Parameter_id AS Id,

                        ParameterName AS NAME,

                        ColumnType AS TYPE,

                        HasDefault,

                        is_output AS IsOutput,

                        TypeName

                FROM    pr a

                CROSS APPLY (SELECT ISNULL('%' + (SELECT Util.dbo.StringConcat (ParameterName, '%') FROM pr b WHERE b.parameter_id < a.parameter_id), '') + '%'

                                    + ParameterName + '%=' + '%' + CASE WHEN parameter_id = MaxParam THEN @WhiteSpace + 'AS' + @WhiteSpace + '%'

                                                                        ELSE (SELECT Util.dbo.StringConcat (ParameterName, '%') FROM pr b

                                                                                        WHERE b.parameter_id > a.parameter_id) + '%'

                                                                   END AS ptt) b

                CROSS APPLY (SELECT SIGN (PATINDEX (ptt, @DeclareSQL)) AS HasDefault) hd

     

    AGAIN:

    SELECT  @SelValues = CASE WHEN @TableExists = 1 THEN 'INSERT #sp_ParamDefault(Id, Name, Type, HasDefault, IsOutput, Value)

    '                         ELSE ''

                         END + 'SELECT * FROM (VALUES' + Util.dbo.StringConcat('(' + CAST(Id AS VARCHAR) + ', ''' + Name + ''', ''' + Type + ''', '

                                                                               + CAST(HasDefault AS VARCHAR) + ', ' + CAST(IsOutput AS VARCHAR) + ', '

                                                                               + CASE WHEN TypeName NOT LIKE '%char%' THEN 'CAST(' + name + ' AS VARCHAR(MAX))'

                                                                                      ELSE name

                                                                                 END + ')', ',

    ') + '

    ) d(Id, Name, Type, HasDefault, IsOutput, Value)',

            @ExecString = 'EXEC #sp_ParamDefaultProc

    ' + ISNULL(Util.dbo.StringConcat(CASE WHEN HasDefault = 0 THEN Name + ' = NULL'

                                     END, ',

    '), '')

    FROM    @ParamTable

     

    SET @SQL = 'CREATE PROCEDURE #sp_ParamDefaultProc

    ' + SUBSTRING(@DeclareSQL, CHARINDEX(@FirstParam, @DeclareSQL), LEN(@DeclareSQL)) + '

    ' + @SelValues

     

    IF OBJECT_ID('TEMPDB..#sp_ParamDefaultProc') IS NOT NULL 

        DROP PROCEDURE #sp_ParamDefaultProc

    EXEC(@SQL)

     

    BEGIN TRY

        EXEC(@ExecString)

    END TRY

    BEGIN CATCH

        SELECT  @ErrorStr = ERROR_MESSAGE(),

                @ErrorId = ERROR_NUMBER()

    -- there must have been a comment containing equal sign between parameters

        UPDATE  p

        SET     HasDefault = 0

        FROM    (SELECT PATINDEX ('%expects parameter ''@%', @ErrorStr) AS ii) i

        CROSS APPLY (SELECT CHARINDEX ('''', @ErrorStr, ii + 20) AS uu) u

        INNER JOIN @ParamTable p ON p.name = SUBSTRING(@ErrorStr, ii + 19, uu - ii - 19)

        WHERE   ii > 0

     

        IF @@ROWCOUNT > 0 

            GOTO AGAIN

     

        RAISERROR(@ErrorStr, 16, 1)

        RETURN 30

    END CATCH

    GO

    EXEC sys.sp_MS_marksystemobject 

        sp_ParamDefault

    GO

    Sunday, September 25, 2011 10:48 PM
  • As indicated above, the following system procedure will do the job:

    exec sp_procedure_params_rowset uspGetBillOfMaterials
    

    PROCEDURE_CATALOG PROCEDURE_SCHEMA PROCEDURE_NAME PARAMETER_NAME ORDINAL_POSITION PARAMETER_TYPE PARAMETER_HASDEFAULT PARAMETER_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DESCRIPTION TYPE_NAME LOCAL_TYPE_NAME
    AdventureWorks2008 dbo uspGetBillOfMaterials;1 @RETURN_VALUE 0 4 0 NULL 0 3 NULL NULL 10 NULL NULL int int
    AdventureWorks2008 dbo uspGetBillOfMaterials;1 @StartProductID 1 1 0 NULL 1 3 NULL NULL 10 NULL NULL int int
    AdventureWorks2008 dbo uspGetBillOfMaterials;1 @CheckDate 2 1 0 NULL 1 135 NULL NULL NULL NULL NULL datetime datetime


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Monday, September 26, 2011 12:19 AM
  • The output of procedure exec sp_procedure_params_rowset has nulls for defaults.
    I created two version of code to resolve this issue, the first using a sql lexer in a clr parsed the parameter defaults and returned them as a table valued clr, this works better in some cases since it's identical to declaration, for example if the param declare is (@Name int = @@ServerName) then you get the text right, but for some reason for binary declarations (@Id binary(10) = 0x45) it failed to return the string wwell, probably another lexer may not have the but, for for  most cases it worked perfect.
    The second version is the I cut out the variable declaration, created a temp proc with the variable declaration  which retunrs a union all of all the parameter out (or put them in a temp table) etc, this one is more accurate for binary etc but if the default was  (@Name int = @@ServerName) I do loose the original string.

    Gokhan Varol
    Saturday, October 01, 2011 6:46 PM