Home
Optional parameters in Stored Procedure

Optional parameters in Stored Procedure

September 12, 2008 Programming 5 Comments

When you create a store procedure on Microsoft SQL Server with some input parameters, sometimes you don’t want to assign all of parameters (optional parameters) so you need to specify default value for the parameters. Otherwise, it shows error when execute the store procedure. Let’s see some example, I have created a new store procedure to query product name from Northwind database which require 2 input parameters. The script to create a sample store procedure is below:

CREATE PROCEDURE sp_getproduct
		(@UnitPrice money,
		 @UnitsInStock SMALLINT = '10')
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT [ProductName] FROM Products
		WHERE ([UnitPrice] < @UnitPrice) AND
			  ([UnitsInStock] > @UnitsInStock)
END
GO

Sample Store Procedure

So as long as I specify both input parameters, the query is OK.
Specify Input Parameters

But if I miss an input parameter, the error will be shown as the figure below:
Missing Input Parameters

Solution

  1. You can define the default value for parameters to avoid this error message. The default value generally is NULL. But you also can define other value as you want.
  2. In this example, I’ve altered the store procedure by assign default value for these parameters to be NULL and also in WHERE clause. 
    ALTER PROCEDURE sp_getproduct
		(@UnitPrice money = NULL,
		 @UnitsInStock SMALLINT = NULL)
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT [ProductName] FROM Products
		WHERE ((@UnitPrice IS NULL) OR ([UnitPrice] < @UnitPrice)) AND
			  ((@UnitsInStock IS NULL) OR ([UnitsInStock] > @UnitsInStock))
END
GO

    Code Explanation:

    • I’ve altered the parameters declaration to assign the NULL value as the default value. 
      		(@UnitPrice money = NULL,
		 @UnitsInStock SMALLINT = NULL)
    • Next, I’ve altered in WHERE clause to be TRUE if the parameters are NULL. So if the both parameters are NULL, the SELECT statement will return all ProductName from Products table. 
      		WHERE ((@UnitPrice IS NULL) OR ([UnitPrice] < @UnitPrice)) AND
			  ((@UnitsInStock IS NULL) OR ([UnitsInStock] > @UnitsInStock))

    Optional Parameters

  3. Re-execute the query again, you’ll see the error message is gone.
    Execute SP Optional Parameters

5 Comments

  1. Priya May 7, 2009

    I cant understand with this example

  2. linglom May 12, 2009

    Hi, Priya
    The example above is to execute the store procedure without specify parameters by set default values (NULL) to the parameters.

  3. Jonathan May 20, 2009

    Hi,
    I’m having problms using optionals. I’m using 3 params in my stored proc. Param 1 is required but params 2 and 3 are optionals. I can run my stored proc fine if I omit both params 2 and 3. But if I omit param 2 and pass a value to param 3, SQL Server complains:
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘,’.

    The syntax I used is:
    exec usp_StoredProcA param1,, param3

    If I do the following:
    exec usp_StoredProcA param1, ”, param3

    param 2 is interpreted as empty string and not the default value that I specified.

    Sorry for the long email. Hope I explained myself clearly.

  4. linglom May 20, 2009

    Hi, Jonathan
    Specify parameter name can solve your problem.

    For example,
    EXEC [dbo].[sp_getproduct] @UnitsInStock = 20
    exec usp_StoredProcA param1 = 10, param3 = 20

  5. Mehboob Ali Yousafzai October 14, 2010

    Sir,if suppose i had a store procedure to insert two fields in a table,
    Create procedure test
    @a int =null,
    @b nvarchar(50) =null
    as
    insert into tblName(col1,col2)
    values(@a,@b)
    go

    Now when i execute the following line it successfull
    exec test
    & also
    exec test 1,’Swabi’
    But if i only want to insert nvarchar(50) data i.e in @b(col2),then what should i do?
    exec test ‘Swabi’ –error,as first argument is integer missing…

    Can any one help me plz !!!

Leave a Reply