Default Values and Named Parameters for Stored Procs

It's always interesting to guess what the response to an article will be - will it be widely read, get a good rating, draw lots of comments (hopefully support ones)? So, before we'll start, I'll guess that 6 of 10 will agree with my thoughts. We'll see!

SQL allows you to enter defaults for your stored proc parameters, like this:

If you just run usp_testparams with NO parameters, you'll get the following:

andy warren 99

Or you can just specify one of the two params, using the named parameter syntax:

usp_testparams @param2 = 88

Which returns...

andy warren 88

And of course you can pass both parameters, either using named parameters or just passing by ordinal:

So far I've tried not to give you much of a hint about what I'm thinking. Would you consider setting default values a good practice? A useful option? What about the named parameter syntax rather than just ordinal addressing?

Ready?

Let's start with named parameters. I see them as being moderately useful, mainly for procedures that have a long list of parameters where you probably only need to use a few. An example is xp_sendmail. Take a look at the calling syntax:

xp_sendmail {[@recipients =] 'recipients [;...n]'}
    [,[@message =] 'message']
    [,[@query =] 'query']
    [,[@attachments =] 'attachments [;...n]']
    [,[@copy_recipients =] 'copy_recipients [;...n]'
    [,[@blind_copy_recipients =] 'blind_copy_recipients [;...n]'
    [,[@subject =] 'subject']
    [,[@type =] 'type']
    [,[@attach_results =] 'attach_value']
    [,[@no_output =] 'output_value']
    [,[@no_header =] 'header_value']
    [,[@width =] width]
    [,[@separator =] 'separator']
    [,[@echo_error =] 'echo_value']
    [,[@set_user =] 'user']
    [,[@dbuse =] 'database']

The majority of the time I need a recipient, subject, and message, none of the rest of the stuff, so I'll run something like this:

xp_sendmail @recipients ='andy', @subject='Test', @message='This is a test message'

For ad hoc stuff, that makes sense, I can just enter the parameters I need. Even when calling procs in code, either from other procs or directly from applications, if I don't need everything, why pass it? Most languages support the named parameter syntax, including VB. Which should you use? While probably less robust (because param order might get altered), I tend to pass params by ordinal when coding in procs, in ADO I always declare all the parameters up front rather than doing a parameters.refresh, then I address the params I need by name.

Default values? Bah! Looking back at my admittedly trivial example, what do I gain from setting defaults? In code, I'm going to either set the parameter each time or not set it (ok, you could do some conditional code to set only params for which you had values, but why?). It's one thing to allow null values for parameters, totally acceptable even since you're not always going to have every value (think about adding a contact, you may not know every bit of their demographic info). If I don't have the value, then isn't a null good enough? Maybe a reason would be you have a column that doesn't allow nulls, so you're thinking - hey, I'll just set this to an empty string or 'NA' or 'Unk' or something, if they don't know it, I'll get my default. Nope. You get that only if they don't set the param. Nothing to stop them from passing a null to the param, so you still need code inside the proc to handle the nulls. Bottom line? Just say no to default values!

I don't think using or not using either of these counts as either a best or worst practice. Like most options, sometimes they are handy, sometimes not. My goal was to get you thinking about how you call procs and get you to reassess your options. As always, I look forward to your comments both good and bad - I usually learn as much from you as you do from me! Don't forget my lead in either, I'm still betting 6 out of 10 agree with me:-)