T-SQL: Stored Procedure Default Values

Please see my other Database Development articles.

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.


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”).


Now, I’ll execute my sproc three times.


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.


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 )

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