T-SQL: Stored Procedure Default Values

Building flexibility into data INSERT operations.

Often when designing an application that receives user input, it’s difficult to determine beforehand exactly which fields will be supplied where a user completes a form.
SQL Server provides the ability to supply default values for parameters of stored procedures to allow for more flexibility when supplying values to stored procedures.

To illustrate this features, I’ll begin with a simple table for Sales Contacts where I enabled NULL values on those fields where I’m unsure if those values will always be supplied by the user – user in this case would be a form or webpage.

1-tsql-sproc-default-values

Next, I’ll build a simple sproc to INSERT a new record while supplying default values for those parameters I might not always have values for when calling the sproc (“=null”).

2-tsql-sproc-default-values

Now, I’ll execute my sproc three times.

3-tsql-sproc-default-values

The first time I’ll supply values for all parameters, but for the second call, I’ll leave the last value off – @title, and for the third call, I’ll provide values for neither the @title or @phone_ext parameters.
As you can see, after executing all three sproc calls, when I didn’t supply a value for a parameter that had a default value supplied, that value was assigned for that record.

4-tsql-sproc-default-values

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s