Default Values in Stored Procedure Parameters: Do you know this rule?

It is very easy to set up a default value for an input parameter in a stored procedure.

When we establish a default value for an input parameter, it becomes optional to provide a value for that parameter when calling the procedure. In that event, the value used for the parameter is whatever the default value is.

To work through the example in this tutorial, let’s create a Books table:

CREATE TABLE Books
(
BookID INT IDENTITY,
Author VARCHAR(20),
Title VARCHAR(50),
InStock BIT,
DateAdded DATETIME
)
GO

Here is a simple example of a stored procedure that inserts a row into the Books table. It takes several input parameters, and those values are used in an internal INSERT statement:

CREATE OR ALTER PROCEDURE InsertNewBook(@author VARCHAR(20), @title VARCHAR(50), @instock BIT, @adddate DATETIME)
AS
INSERT INTO Books(Author, Title, InStock, DateAdded)
VALUES (@author, @title, @instock, @adddate)

SELECT * FROM Books
GO

Here is an example of using this procedure to add a new book to our table:

(We don’t need to provide a value for the BookID column because that column uses the IDENTITY property)

Let’s say we wanted to make the specification of the InStock value optional when calling our stored procedure. If we’re bothering to add a new book to our inventory at all, odds are it is in stock, so outlining the value of the @instock parameter might simply be extra unnecessary work. We’ll simplify things for our users and just give that parameter a default value of 1.

The way we do it is simple. Next to the data type of the input parameter, we just outline the equals sign (=) followed by whatever we want the default value to be. In our case, that will be the number 1. It looks like this:

CREATE OR ALTER PROCEDURE InsertNewBook(@author VARCHAR(20), @title VARCHAR(50), @instock BIT = 1, @adddate DATETIME)
AS
INSERT INTO Books(Author, Title, InStock, DateAdded)
VALUES (@author, @title, @instock, @adddate)

SELECT * FROM Books
GO

Now, if we don’t provide a value for @instock, the value 1 is used:

So simple!

The default value must be either NULL or a constant

One simple rule you need to keep in mind is that the default value must be either NULL or a constant value. The default value cannot be something that is nondeterministic (something that returns a different value every time it’s called).

For example, we cannot establish a default value on our @adddate input parameter to be a call to the GETDATE() system function:

(Don’t try to read too much into the error messages. SQL Server is simply very confused)

Once again, GETDATE() returns a different value every time it’s called, which means it is nondeterministic, which means it cannot be used as a default value for our input parameter!

If we truly wanted to make the specification of the @adddate parameter optional, we could give it a default value of NULL. Then in the body of the procedure, if the value of the parameter is NULL, we can then assign it the result of GETDATE(), like so:

CREATE OR ALTER PROCEDURE InsertNewBook(@author VARCHAR(20), @title VARCHAR(50), @instock BIT = 1, @adddate DATETIME = NULL)
AS
IF @adddate IS NULL
BEGIN
SET @adddate = GETDATE()
END

INSERT INTO Books(Author, Title, InStock, DateAdded)
VALUES (@author, @title, @instock, @adddate)

SELECT * FROM Books
GO

Here’s what a call to the procedure would look like:

(Writing this on March 4th @7:12 A.M.)

So simple!

Next Steps:

Leave a comment if you found this tutorial helpful!

Make sure you download your copy of the following FREE Ebook:

FREE Ebook on SQL Server Stored Procedures!

This FREE Ebook contain absolutely everything you need to know about Stored Procedures in Microsoft SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!