optional parameters in stored procedure

• sqlquery-101401

  SSCarpal Tunnel

  Points: 4951

  More actions

  March 4, 2004 at 7:19 am

  #107751

  plz anybody suggest how i can pass optional parameter in stored procedure,

  can somebody suggest the syntax

  Thanks

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 4, 2004 at 7:36 am

  #497283

  BOL covers a topic on optional parameters. Also see my reply on your other thread. That is the example from BOL

  

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Dev7

  SSC Veteran

  Points: 270

  More actions

  March 5, 2004 at 7:01 am

  #497533

  I used coalesce in my stored procedure which has many optional input parameters as follows:

  CREATE PROCEDURE sp_Example_Get

  @inparm1INT= NULL

  ,@inparm2CHAR(1)= NULL

  ,@inparm3VARCHAR(10)= NULL

  AS

  BEGIN

  Set NOCOUNT On

  If @inparm1 = ''

  Set @inparm1= null

  If @inparm2= ''

  Set @inparm2= null

  If @inparm3= ''

  Set @inparm3= null

  Begin

  Select *

  FromEXAMPLE

  Wheretabval1= coalesce(@inparm1, tabval1)

  Andtabval2

  = coalesce(@inparm2, tabval2)

  Andtabval3

  = coalesce(@inparm3, tabval3)

  return 0

  end

  end