Scope


This article’s attention will be principals in executing stored procedures, with focus on stored procedure parameters. This is not an article about security, recompile, etc. It’s about calling stored procedures with various type of parameters. 

Introduction

The complex part after writing a stored procedure is how to call it. In many cases, caller of the stored procedure is not a person who wrote it. He/She may not have full permission to see stored procedure definition while he/she can call it. So, we need to have a clear protocol to call stored procedures that works fine in all conditions. To achieve this, we will see how to pass parameter to a stored procedure. There are lots of questions related to the calling stored procedures like this question in Transact-SQL MSDN Forum which could be a good reason for writing this article.

Calling a stored procedure without parameter

We can call a stored procedure using “EXECUTE” keyword or its abbreviation “EXEC”. For example, supposing that we have a stored procedure with the name “CallMe”. We created it using the following code:

CREATE PROC CallMe
AS
PRINT 'This is the first time I call a Stored Procedure!';
GO

We can call this stored procedure using this code:

EXECUTE CallMe;
EXEC CallMe;

Warning

If calling a stored procedure is the first statement in a batch, there’s no need to use EXEC keyword. So, if we call this stored procedure as following, it successfully executes!

CallMe;
GO
              
This is indeed a bad practice to fall into the habit to remove EXEC keyword even in a temporary batch. This habit can lead to get some errors while changing scripts. Moreover, it can change the script behavior in some cases. For instance, in the following code we just add a select statement into the script. We expect it still works fine.

SELECT *
FROM   sys.objects
 
CallMe;
GO

Although, it does not raise any error, it does not execute the stored procedure "CallMe". It just executes the select statement, because the phrase "CallMe" would be an alias for the table in the above select statement.

Calling a stored procedure with input parameter

There are two general patterns for calling a stored procedure which has parameter(s);

  1. Based on the parameter position
  2. Based on the parameter name

To examine these two patterns, first we create a sample stored procedure using the following code:

CREATE PROC FullName
  @FristName NVARCHAR(100),
  @LastName NVARCHAR(100)
AS
PRINT 'Full name is ' + @FristName + SPACE(1) + @LastName + '.';
GO

Now, we can call this stored procedure using these two patterns, and see their strengths and weaknesses. 

Based on the parameter position

This type of calling is based on the parameters position in the stored procedure definition. In the definition of former sample stored procedure, @FirstName is the first and @LastName is the second parameters. If we want to call this stored procedure based on its parameters position, we must keep the order of the parameters in the same order which they are declared in the stored procedure. So, if we call this stored procedure like follow code, we get correct full name, while if we change the parameter order, the result will be wrong.

EXEC FullName
  'Saeid',
  'Hasani';

Next figure shows output result after executing above code.


Based on the parameter name

This explicit calling style is based on the parameters name in the stored procedure definition. Unlike previous calling form, position of the parameter does not considered in this calling  style. To call the sample stored procedure using such style, we can rewrite former code like this one:

EXEC FullName
  @LastName = 'Hasani',
  @FristName = 'Saeid';

The output result is the same as the result getting by calling the sample stored procedure based on its parameter position. We deliberately changed the order of the parameters when rewriting the above code. Because this is an explicit calling, every parameter passes by its name, leading to correct result despite of its position.

Using the calling style which is based on the parameter name leads to having clean and maintainable code. Supposing that a stored procedure has many parameters and we have to change the parameters order, adding new ones or removing some of them. It is conceivable that calling this stored procedure based on the parameters position will become hard to debug. Even worse, if we have to debug a set of nested stored procedures, it will become more difficult than before. If we remove some parameters, we also have to change all callings in the nested stored procedures. If we use calling by parameter name style, we can change it in all nested stored procedures as fast as possible.

Calling a stored procedure with parameter that has default value

We can assign a default value to a parameter in the stored procedure definition. We will see few examples to be familiar with how to define default value and how to call stored procedure with such parameter type.

As simplest example, we assign value 2 to @Param parameter in the following code:

CREATE PROC DefaultValue
  @Param int = 2
AS
SELECT @Param AS [Default]

There are three ways to call this stored procedure as shown in the next code:

EXEC DefaultValue DEFAULT;
EXEC DefaultValue @Param = DEFAULT;
EXEC DefaultValue ;

First calling form is based on the parameter position and second is based on the parameter name, both using DEFAULT keyword which illustrates the parameter has a default value that we want to use it. The third calling form is still correct, because this stored procedure has one single parameter which has a default value. Therefore, if we call it without any parameters, it will assign value 2 to the parameter @param.

Now, we can see another sample stored procedure which has two parameters. The second parameter has NULL as its default value, as shown in the next code:

CREATE PROC uspDefaultValue2
  @Param1 INT,
  @Param2 INT = NULL    /* This parameter has default value. */
AS
SELECT @Param1 AS Param1,
       @Param2 AS Param2 ;
GO

We can call this stored procedure in the four patterns, like the following code:

--1
EXEC uspDefaultValue2 23,
                      DEFAULT ;
--2
EXEC uspDefaultValue2 @Param1 = 23,
                      @Param2 = DEFAULT ;
 
--3
EXEC uspDefaultValue2 23 ;
 
--4
EXEC uspDefaultValue2 @Param1 = 23 ;
 
GO

First calling form is based on the parameter position and the second one is based on the parameter name. However, the third calling form is acceptable. It’s somehow like the first calling form, with a little different that is the second parameter in this calling form has a default value and there is no parameter in the stored procedure after this one which has not a default value, so we can remove it in such calling form. However, there is no need using the DEFAULT keyword in this calling form. The fourth calling form is based on the parameter name with removing the last parameter, as we saw in the former calling style.

Supposing the we have a stored procedure that has three parameters which second one has a default value. Next code shows its creation script.

CREATE PROC uspDefaultValue3
  @Param1 INT,
  @Param2 INT = NULL,    /* This parameter has default value. */
  @Param3 INT
AS
SELECT @Param1 AS Param1,
       @Param2 AS Param2,
       @Param3 AS Param3 ;
GO

Now, we can try four calling forms expressed in the former example. These calling forms are shown in the following code:

--1
EXEC uspDefaultValue3 23,
                      DEFAULT,
                      10 ;
--2
EXEC uspDefaultValue3 @Param1 = 23,
                      @Param2 = DEFAULT,
                      @Param3 = 10 ;
 
--3
EXEC uspDefaultValue3 23,
                      10 ;
 
--4
EXEC uspDefaultValue3 @Param1 = 23,
                      @Param3 = 10 ;
 
GO

We want to pass value 23 to the the first parameter and value 10 to the third one. In the above code, the third calling form is not valid. It passes value 10 to the second parameter and the third one still has no value in such calling form.

Using these examples we can conclude that,

Calling store procedures explicitly, based on the parameter(s) name without ignoring parameters which has a default value is safer and better than calling it implicitly, based on the parameter(s) position, or removing parameters which has a default value.


The next example emphasizes this conclusion. 
We start this example using the following code:

CREATE PROC uspDefaultValue4
  @Param1 INT = NULL,    /* This parameter has default value. */
  @Param2 INT,
  @Param3 INT
AS
SELECT @Param1 AS Param1,
       @Param2 AS Param2,
       @Param3 AS Param3 ;
GO

Supposing that we use the calling form based on the parameter position, and ignoring those parameters that have a default value, but missing two parameters when calling the stored procedure. We just get an error which inform us the parameter @Param2 is needed in the execution statement.

EXEC uspDefaultValue4 23 ;


So, we can fix it and add just one value for that parameter using the following code:

EXEC uspDefaultValue4 23, 10 ;


As illustrated in the above picture, we found that there is another parameter which we still did not pass it. So, each time we can find the first expected parameter which was not supplied. If the procedure had over 100 parameters, for example, and we insist on calling it based on the parameter position, it could be frustrating job every time that we need to change some parameters.

Tips and tricks

There are some tips around using stored procedure parameter(s). As programmers, we usually like this section. So, let’s having fun with these tips!

Parameter Nullability

When creating a table, we can use NOT NULL for the column(s) to avoid using NULL value. But we cannot use NOT NULL for the parameters in common stored procedures, except for the natively compiled stored procedures which we don’t want to talk about in this article. If we try using NOT NULL in the parameter definition like the following code, we will get an error, as we see in the next picture, that informs us it's impossible to set a stored procedure parameter as NOT NULL.

CREATE PROC uspForceNotNull
  @Param INT NOT NULL
AS
SELECT @Param AS Param ;
GO


A workaround to this problem might be to simply validate that parameter in the stored procedure body. We can raise a custom error when getting NULL value for such parameter like the following code:

CREATE PROC uspForceNotNull
  @Param INT
AS
IF @Param IS NULL
    BEGIN
        RAISERROR ( N'@Param cannot be NULL!', 16, 1 )
        RETURN 1;
    END
SELECT @Param AS Param ;
GO
 
--calling with NULL
EXEC uspForceNotNull @Param = NULL

The result of the passing NULL will be like this picture.


Function as default value

We cannot use functions as default value. For instance, if we want to set the function GETDATE() as a default value for the stored procedure parameter, we will get an error. The default value for a parameter must be constant or NULL. To achieve the goal of using functions, for example GETDATE(), as a default value, first we define parameter with NULL as its default value, then we assign it in the stored procedure body whenever it passes by NULL. Otherwise, we leave it as is. The following code shows this pattern:

CREATE PROC uspFunctionAsDefaultValue
  @CallingDate DATETIME = NULL
AS
IF @CallingDate IS NULL
    BEGIN
        SET @CallingDate = CURRENT_TIMESTAMP    /* CURRENT_TIMESTAMP or GETDATE() */
    END
SELECT @CallingDate AS CallingDate ;
GO

While writing this article, I faced this question in the Transact-SQL MSDN Forum which could be the other sample for this section. 

String without quotes

Supposing that we define a parameter with string data type such as NVARCHAR. We can assign a default value for this parameter usually using quotes. So, the following code must encounter an error:

CREATE PROC uspStringWithoutQuotes
  @Param NVARCHAR(20) = Hello
AS
PRINT @Param + '!';
GO
               

Although, we assign the string "Hello" without using quotes, this code will create the stored procedure. If we call this stored procedure with using the default value for the parameter, it passes the string "Hello" to the parameter @Param, like follow picture:

EXEC uspStringWithoutQuotes @Param = DEFAULT;
GO


Although, I reported this problem as a bug in this link, the SQL Server Development Team expressed that while they agree that this behavior is not optimal, it has been in the product for a long time and therefore, they cannot easily change it, as it would break existing applications. Thanks to their fast response, we must avoid using this pattern in our codes until it will be fixed. 


Calling a stored procedure with output parameter

To define an output parameter for a stored procedure, we can simply add OUTPUT or OUT keyword  just after the parameter definition in the stored procedure. We can do it like this code:

CREATE PROC uspTestOutput
  @Param INT OUTPUT
AS
SET @Param = ISNULL(@Param, 0) + 10 ;
GO

If we want to use the output value for the parameter after calling the stored procedure, we must also add OUTPUT or OUT keyword when passing that parameter. If we would like to call the stored procedure based on the parameter name, we can use the next code:

DECLARE @NewParam INT;
 
EXEC uspTestOutput @Param = @NewParam OUTPUT;
 
SELECT @NewParam AS OutputValue;
GO

On the other hand, the calling style which is based on the parameter position will be like the following code:

DECLARE @NewParam INT;
 
EXEC uspTestOutput @NewParam OUTPUT;
 
SELECT @NewParam AS OutputValue;
GO

Therefore, after calling the stored procedure we can get the output value and using it.


Return Value

Return value is not an output parameter. Each stored procedure has a return value which its data type is INTEGER. By default, the return value would be "zero", except we change it manually in the stored procedure body. It’s a good practice to set a protocol in our software application development patterns to use the return value in all stored procedures. Return a non-zero value is usually consider as encounter an error when executing the stored procedure, while return the value "zero" means a successful execution. Former in this article we created the following stored procedure:

CREATE PROC uspForceNotNull
  @Param INT
AS
IF @Param IS NULL
    BEGIN
        RAISERROR ( N'@Param cannot be NULL!', 16, 1 )
        RETURN 1;
    END
SELECT @Param AS Param ;
GO

We can recall it to get its return value, when passing NULL to the parameter @Param. We can do this using this code:

DECLARE @ReturnValue INT;
 
EXEC @ReturnValue = uspForceNotNull @Param = NULL
 
SELECT @ReturnValue AS ReturnValue;

Although, this is a strange calling style but it is usually used in many stored procedures we might involve with.


Conclusion

Stored procedure parameters could have other properties which are more specific than we explained in this article. As an example, we can define read only parameters which is applicable just for the parameters with the table valued parameters. We can pass a set of elements to this type of parameters. So, this article is not cover all things about the stored procedure parameters. It’s an introduction to this important topic which describes some important principals.


See Also