Details About Parameter Passing

There’s a little more to parameter passing. Let’s consider a procedure that takes three parameters:

create proc sample
(@a int, @b int, @c int)
as
select c1, c2, c3
from   t1
where  a = @a
and    b = @b
and    c = @c
return

To execute the procedure, you can pass the parameters by position, as we’ve been doing, or you can pass the parameters by name. Here’s how you would pass them by position:

execute sample 1, 2, 3

In order to pass values by name, you would provide the actual parameter names:

execute sample @b = 2, @a = 1, @c = 3

You can mix both styles, but once you start passing by name, you must pass all the rest of the parameters in the list by name. This example is wrong because it passes a parameter by position later in the list than it passes one by name:

/* This won’t work */
execute sample @b = 2, 1, 3

This example works because the first parameter is passed by position, where only later parameters are passed by name:

/* This will work */
execute sample 1, @c = 3, @b = 2

---

Tip:  For years I thought that passing by name was better than by position—and from a coding standpoint, it is better. It’s not unusual for a stored procedure to have 20 or 30 parameters. It’s useful for someone reading your application to be able to tell immediately which values correspond to which parameters.

However, procedure execution is faster when you pass parameters by position rather than name. If you want to get the best of both worlds, consider documenting complex procedure calls with the parameter name, as in this example:

execute  sample
     1,         -- @a
     2,         -- @b
     3          -- @c

---

Parameters and Quoted Values

When you pass a char or varchar parameter to a procedure, quotes aren’t required unless one of these three conditions is true:

•  The parameter value contains any punctuation, special character, or a space (“.”, “,”, “-”)

•  The parameter value is a reserved word (“default”, “null”)

•  The parameter value is a string consisting entirely of numbers (“1234”)

Notice that dates need to be passed as quoted strings because the string contains a slash (“/”).

Syntax for Passing Parameters

Here’s the detailed syntax for the EXECUTE statement with parameters:

[exec[ute]] parameter-name [@parameter-name =] parameter-value [, …]

Parameter Defaults

What happens if you try to execute the procedure without passing any parameter values? The parameter execution fails.

pr_getprice2

Msg 201, Level 16, State 2
Procedure pr_getprice2 expects parameter @title_id, which was not
supplied.

The parameter is required, so the execution fails. But think about the system procedures like sp_help. They operate in multiple modes, working whether you pass a parameter or not. What is the difference? The key is that the system procedures have set up their parameters with default values, making them optional.

To make a parameter optional, assign it a default value in the parameter declaration, like this:

(@parameter-name datatype = default-value)

Here’s the new procedure header for the next version of pr_getprice:

create proc pr_getprice2
(@title_id char(6) = null)
as

It’s common to set the default value to null and then test for null in the procedure:

if @title_id is null

But what action do you take in this case? You can’t pass the null to the query—you won’t find any rows. So what can you do? Here are some choices:

•  Pass an error message to the user with guidelines on the proper way to execute the procedure

•  Display a list of possible title_id values

•  Display an overall average or summary price

So how do you choose which action to take? That depends on the requirements of your application. We’ll look at the code for each alternative so that you can see how it’s done.

Parameter Defaults and Customized Error Messages

Here’s the approach that returns an error message:

create proc pr_getprice3a
(@title_id char(6) = null)
as
if @title_id is null
begin
   print “The procedure pr_getprice3 requires a parameter, title_id”
   return
end
select  price
from    titles
where   title_id = @title_id
return

Test the procedure by executing it without a parameter:

pr_getprice3a
The procedure pr_getprice3 requires a parameter, title_id

Parameter Defaults and Listed Values

Here’s the approach that returns a list of values for title_id:

create proc pr_getprice3b
(@title_id char(6) = null)
as
if @title_id is null
begin
   select  title_id
   from    titles
   return
end
select  price
from    titles
where   title_id = @title_id
return

Again, test execution without a parameter:

pr_getprice3b

title_id
--------
BU1032
BU1111
BU2075
…
TC7777