Post reply

can i specify optional parameters in stored proc

  • June 24, 2004 at 1:33 am

    #111807

    can i make optional parameters in stored procedure

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • June 24, 2004 at 6:53 am

    #511769

    I dont think u can use optional parameters but a way to achieve this would be to use variables with default values of Null. But you need to check for the value in the variable before using them in your queries.

    Cheers,


    Arvind

  • June 24, 2004 at 1:01 pm

    #511857

    Sure you can!  Just set default values, and execute the procedure sending the parameters by name. 

    CREATE PROCEDURE Parm_Example

     @Parm1 varchar(100) = NULL,

     @Parm2 varchar(4000) = 'Parm2 value',

     @Parm3 varchar(100)= 'Parm3 value',

     @Parm4 varchar(100) = NULL

    AS

    -- rest of the proc here...

    then to execute...

    EXECUTE Parm_Example @Parm2 = 'alternate value', @Parm4 = 'some other value'

    @Parm1 and @Parm3 will retain their default values.

    Steve

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

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