Specifying a Default Value

Specifying a 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

This example shows the get_sales_for_title procedure with special handling for cases when the stored procedure is executed without a value for the @title parameter:

CREATE PROCEDURE get_sales_for_title
@title varchar(80) = NULL,  -- NULL default value
@ytd_sales int OUTPUT
AS  

-- Validate the @title parameter.
IF @title IS NULL
BEGIN
   PRINT 'ERROR: You must specify a title value.'
   RETURN
END

-- Get the sales for the specified title and 
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title

RETURN
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:

CREATE PROCEDURE my_proc
@first int = NULL,  -- NULL default value
@second int = 2,    -- Default value of 2
@third int = 3      -- Default value of 3
AS

-- Display values.
SELECT @first, @second, @third
GO

EXECUTE my_proc                -- No parameters supplied
GO

Displays:

NULL  2  3

EXECUTE my_proc 10, 20, 30     -- All parameters supplied
GO

Displays:

10  20  30

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

Displays:

NULL  500  3

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

Displays:

40  2  50

See Also

EXECUTE