• Article

Specifying a Parameter Default Value

You can create a stored procedure with optional parameters by specifying a default value for optional parameters. When the stored procedure is executed, the default value is used if no other value has been specified.

Specifying default values is necessary because a system error is returned if a parameter does not have a default value specified in the stored procedure and the calling program does not provide a value for the parameter when the stored procedure is executed.

If no value can be specified appropriately as a default for the parameter, you can specify NULL as the default for a parameter and have the stored procedure return a customized message if the stored procedure is executed without a value for the parameter.

Note

If the default value is a character string that contains embedded blanks or punctuation, or if it begins with a number (for example, 6xxx), it must be enclosed in single, straight quotation marks.

Examples

The following example creates the usp_GetSalesYTD procedure with one input parameter, @SalesPerson. NULL is assigned as the default value for the parameter and is used in error handling statements to return a custom error message for cases when the stored procedure is executed without a value for the @SalesPerson parameter.

USE AdventureWorks;
GO
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL
    DROP PROCEDURE Sales.uspGetSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetSalesYTD
@SalesPerson nvarchar(50) = NULL  -- NULL default value
AS 
    SET NOCOUNT ON; 

-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
BEGIN
   PRINT 'ERROR: You must specify the last name of the sales person.'
   RETURN
END
-- Get the sales for the specified sales person and 
-- assign it to the output parameter.
SELECT SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.EmployeeID = sp.SalesPersonID
WHERE LastName = @SalesPerson;
RETURN
GO

The following example executes the stored procedure. The first statement executes the stored procedure without specifying an input value. This causes the error handling statements in the stored procedure to return the custom error message. The second statement supplies an input value and returns the expected result set.

-- Run the stored procedure without specifying an input value.
EXEC Sales.usp_GetSalesYTD;
GO
-- Run the stored procedure with an input value.
EXEC Sales.usp_GetSalesYTD N'Blythe';
GO

The following example shows the my_proc procedure with default values for each of the three parameters @first, @second, and @third, and the values displayed when the stored procedure is executed with other parameter values:

IF OBJECT_ID('dbo.my_proc', 'P') IS NOT NULL
    DROP PROCEDURE dbo.my_proc;
GO
CREATE PROCEDURE dbo.my_proc
    @first int = NULL,  -- NULL default value
    @second int = 2,    -- Default value of 2
    @third int = 3      -- Default value of 3
AS 
    SET NOCOUNT ON;
    SELECT @first, @second, @third;
GO

EXECUTE dbo.my_proc; -- No parameters supplied
GO

Here is the result set.

NULL  2  3

EXECUTE dbo.my_proc 10, 20, 30;-- All parameters supplied
GO

Here is the result set.

10  20  30

EXECUTE dbo.my_proc @second = 500;  -- Only second parameter supplied by name
GO

Here is the result set.

NULL  500  3

EXECUTE dbo.my_proc 40, @third = 50 -- Only first and third parameters
                                    -- are supplied.

Here is the result set.

40  2  50