Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Unanswered: Optional Parameters in a Stored Proc?

    Title speaks for itself really.
    Is it possible to write a stored proc with optional parameters?
    For example consider the following SELECT

    SELECT FLD1, FLD2 FLD3 FROM TBL1

    I'd like to add optional parameters to that statement so that if they wanted to narrow down the results by providing criteria for some fields they could - but didn't have to.

    Is this possible?

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    DECLARE @optional_fld1 VARCHAR(55)

    SELECT @optional_fld1 = 'test'

    SELECT FLD1, FLD2, FLD3 FROM TBL1 WHERE (@optional_fld1 IS NULL OR FLD1 = @optional_fld1)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    In such cases I add parameter with default value. If value is equal to default, then parameter wasn't specified. You have to be sure that default value can't be choosen as specified value.

    INSERT INTO T_Cup SELECT coffee, sugar, hot_water, milk
    EXEC sp_stir
    EXEC sp_drink
    GO

  4. #4
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    Thanks For your help thus far but I have to admit thus far I don't really understand that SQL Statement.
    Specifically this bit....
    (@optional_fld1 IS NULL OR fld1 LIKE @optional_param1)

    My Stored Proc is thus far like so (I've tried adapting derrick's sollution to this):

    CREATE PROCEDURE getPeople
    @optional_param1 varchar(50)
    AS
    begin
    SELECT fld1, fld2 FROM tbl1 WHERE (@optional_param1 IS NULL OR fld1 LIKE @optional_param1)
    end
    GO

    OK - now am I right in saying that....
    That procedure will pull down ALL rows from tbl1 unless a value for @optional_param1 is supllied in which case it will filter by fld1 using @optional_param1 as criteria.

    Thanks

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You just need to supply a default for the parameter:

    CREATE PROCEDURE getPeople (@optional_param1 varchar(50)= Null)
    AS
    begin
    SELECT fld1, fld2 FROM tbl1 WHERE (@optional_param1 IS NULL OR fld1 LIKE @optional_param1)
    end

    You can now call this procedure with the parameter:

    exec getPeople @optional_param1 = 'YourString'

    ...or without:

    exec getPeople
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •