Optional parameters in Stored Procedure

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
  2. linglom May 12, 2009
  3. Jonathan May 20, 2009
  4. linglom May 20, 2009
  5. Mehboob Ali Yousafzai October 14, 2010

Leave a Reply