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 warren99

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:-)