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.