Post reply

Default parameters

  • February 26, 2014 at 1:57 am

    #282946

    Using Microsoft SQL Server 8 R2 Standard Edition (10.50.2550.0), I try to execute the following scrip:

    use reclamations

    go

    select O.name

    , P.name

    , P.system_type_id

    , P.max_length

    , P.has_default_value

    , P.default_value

    FROM sys.parameters P INNER JOIN

    sys.objects O ON O.[object_id] = P.[object_id] AND O.is_ms_shipped = 0 INNER JOIN

    sys.sql_modules SQLM ON SQLM.[object_id] = P.[object_id]

    WHERE O.type = 'P'

    ORDER BY O.name

    , P.parameter_id;

    which seems to work correctly, excepting that the column [has_default_value] contains only zeroes (0) and the column [default_value] contains only NULLs, although many of the stored procedures in the database do have input parameters declared with default values.

    Example:

    CREATE PROCEDURE [dbo].[Proc_Tbl_Reclamations_Images_Select_Ex]

    (

    @Row_ID INT = 0

    , @Type INT = 1

    )

    AS

    BEGIN

    DECLARE @ReturnValue INT

    DECLARE @Status INT

    DECLARE @Criteria INT

    BEGIN TRY

    ...

    The corresponding lines returned by the scrip are:

    [font="System"]

    name name system_type_id max_length has_default_value default_value

    Proc_Tbl_Reclamations_Images_Select_Ex @Row_ID 56 4 0 NULL

    Proc_Tbl_Reclamations_Images_Select_Ex @Type 56 4 0 NULL

    [/font]

    Is there a special way of declaring the default value for a parameter so that it appears in sys.parameters?

    Thank you for your time and have a nice day!

  • February 26, 2014 at 4:14 am

    #1692212

    That value is for CLR procedures, not T-SQL procedures, per the documentation on sys.parameters. You can only pull out the defaults looking at the actual definitions of the procedures.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 26, 2014 at 4:44 am

    #1692223

    Thanks for the info!

    I'll keep on parsing the value returned by OBJECT_DEFINITION(object_id) 🙁

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply