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

The following 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 parameter

GO                              -- supplied

Displays:

40  2  50

See Also

EXECUTE