Post reply

Assign A default value to a SP Parameter incase its passed no value

  • March 24, 2006 at 3:59 am

    #93703

    Hi all,

    Is there a way to assign a default value to a stored procedure parameter to compensate for the possibility of no value being passed to the SP

    for a specific parameter?

    Is this how to do it?

    @parameters varchar(8000) = NULL

    So if nothing gets passed to SP for the parameter @parameters, then it is assigned a NULL value?

    Thanks

    Tryst

  • March 24, 2006 at 4:53 am

    #628556

    From EXECUTE in BOL:

    G. Use EXECUTE with DEFAULT

    This example creates a stored procedure with default values for the first and third parameters. When the procedure is run, these defaults are inserted for the first and third parameters if no value is passed in the call or if the default is specified. Note the various ways the DEFAULT keyword can be used. 

    USE pubs
    IF EXISTS (SELECT name FROM sysobjects 
          WHERE name = 'proc_calculate_taxes' AND type = 'P')
       DROP PROCEDURE proc_calculate_taxes
    GO
    -- Create the stored procedure.
    CREATE PROCEDURE proc_calculate_taxes (@p1 smallint = 42, @p2 char(1), 
          @p3 varchar(8) = 'CAR')
        AS 
       SELECT * 
       FROM mytable

    The proc_calculate_taxes stored procedure can be executed in many combinations: 

    EXECUTE proc_calculate_taxes @p2 = 'A'
    EXECUTE proc_calculate_taxes 69, 'B'
    EXECUTE proc_calculate_taxes 69, 'C', 'House'
    EXECUTE proc_calculate_taxes @p1 = DEFAULT, @p2 = 'D'
    EXECUTE proc_calculate_taxes DEFAULT, @p3 = 'Local', @p2 = 'E'
    EXECUTE proc_calculate_taxes 69, 'F', @p3 = DEFAULT
    EXECUTE proc_calculate_taxes 95, 'G', DEFAULT
    EXECUTE proc_calculate_taxes DEFAULT, 'H', DEFAULT
    EXECUTE proc_calculate_taxes DEFAULT, 'I', @p3 = DEFAULT
     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • March 24, 2006 at 4:54 am

    #628557

    BOL is very clear about this...

     

    CREATE PROCEDURE au_info2

       @lastname varchar(30) = 'bla'

    AS

    this sets 'bla' as the default for @lastname. When executing it looks like this...

    execute au_info2 @lastname = 'blabla'

    now the sp uses 'blabla' and not 'bla'

    or

    execute au_info2

    it now uses the default value for @lastname which is 'bla'

  • March 24, 2006 at 8:34 am

    #628646

    Ok - I got it now.

    Thanks people.

    Tryst

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

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