Passing the “Default” value to a Stored Procedure

Passing the “Default” value to a Stored Procedure

If you’ve done work with stored procedures, you are probably aware that stored procedures have parameters, and that the parameters can be defaulted when you declare them. I was recently caught out due to some application code that checked when a parameter was specified for a stored procedure, if the value for the parameter was NULL then pass in the keyword DEFAULT. The Code assumed that if I had gone to the effort of specifying the parameter but not the value, that I must want the default value of the Stored Procedure. I had expected it would pass in the SQL NULL keyword.

But anyway, our example of the DEFAULT keyword is pretty simple. We’re going to pretend to have the Users table from Stack Overflow, but it looks like this:

CREATE TABLE Users
(
	Id INT PRIMARY KEY IDENTITY(1,1),
	UserName NVARCHAR(50) NOT NULL,
	AboutMe NVARCHAR(MAX) NOT NULL
)

We want to write a stored procedure to insert a new User. Note that the AboutMe is not nullable in this example. If the user doesn’t want to specify an About Me, we want to add the text “Apparently, this user prefers to keep an air of mystery about them” like they do on Stack Overflow (they don’t actually store that, but for this example, let’s pretend they do).

CREATE PROCEDURE CreateUser
    @UserName AS NVARCHAR(50),
    @AboutMe AS NVARCHAR(MAX) = 'Apparently, this user prefers to keep an air of mystery about them.'
AS
BEGIN
    INSERT INTO Users (UserName, AboutMe)
    VALUES
    (	@UserName,
    	@AboutMe
    )
END

Nice and simple, it will insert our new users, and if the @AboutMe isn’t passed in, then we’ll get our default text.

You can now call this stored proc in a few different ways:

EXEC CreateUser @UserName = N'User1',
		@AboutMe = 'Some text from the User'
EXEC CreateUser @UserName = N'User2',
		@AboutMe = DEFAULT
EXEC CreateUser @UserName = N'User3'

And if we select out our data again, we’ll see this:

Note that User2 and User3 have the same AboutMe. The DEFAULT keyword will do exactly what you think it would do, it picks up the default value for the parameter, and passes that through, just like it would if you didn’t specify it. This is also exactly what the Microsoft Docs say.