Daniel Calbimonte

Functions vs stored procedures in SQL Server

February 14, 2017 by

Introduction

Usually DBAs prefer stored procedures in SQL instead of functions in SQL Server. Is this a good practice?

In this article, we will teach how to create stored procedures and functions in SQL Server and show advantages and disadvantages one of each. In our examples, we will use scalar user defined functions aka UDFs. We will show some Table-Valued Functions in the future. CLR functions will not be covered here.

We will include the following topics:

  • Creating a hello world in a stored procedure vs a function
  • Invoking a stored procedure vs invoking a function
  • Using variables in a stored procedure vs a function
  • Reusability
  • Invoking functions/procedures inside functions/procedures

Getting started

1. Creating a hello world in a stored procedure in SQL vs a function

Let’s create a simple “Hello world” in a stored procedure and a function to verify which one is easier to create.

We will first create a simple stored procedure using the print statement in SSMS:

Execute the code and then call the stored procedure in SQL:

If you execute the code, you will be able to see the “Hello World” message:

"Hello world" stored procedure result

Figure 1. “Hello world” stored procedure result

Now let’s try to do the same with a function:

We can call the function using a select:

The function will return the following message:

"Hello world" stored procedure result

Figure 2. “Hello world” using a function

If you compare the code, the function requires more code to do the same thing. The BEGIN and END blocks are mandatory in a function while the stored procedure do not require them if it is just one line. In a function, it is mandatory to use the RETURNS and RETURN arguments, whereas in a stored procedure is not necessary.

In few words, a stored procedure is more flexible to write any code that you want, while functions have a rigid structure and functionality.

2. Invoking a stored procedure in SQL vs invoking a function

You can invoke a stored procedure in different ways:

You can invoke using exec or execute and even you can invoke the stored procedure without the execute statement. You do not necessarily need to specify the schema name.

The functions are less flexible. You need to specify the schema to invoke it (which is a good practice to avoid conflicts with other object with the same name and different schema).

Let’s call a function without the schema:

The message displayed is the following:

Msg 195, Level 15, State 10, Line 20 ‘helloworldfunction’ is not a recognized built-in function name

As you can see, the schema name is mandatory to invoke a function:


3. Using variables in a stored procedure in SQL vs a function

We are going to convert Celsius degrees to Fahrenheit using stored procedures and functions to see the differences. Let’s start with a stored procedure:

Celsius is the input parameter and we are doing the calculations in the select statement to convert to Fahrenheit degrees.

If we invoke the stored procedure, we will verify the result converting 0 °C:

The result will be 32 °F:

Using variables in a stored procedure in SQL  vs a function

Figure 3. Stored procedure in SQL to convert Celsius to Fahrenheit

Let’s try to do the same with a function:

You can call the function created in the following way:

We are converting 0 °C to °F. As you can see, the code is very simple in both cases.

4. Reusability

The main advantage about a function is that it can be reused in code. For example, you can do the following:

In this example, we are concatenating the function of the example 1 with a string. The result is the following:

Reusability of function and stored procedure

Figure 4. Concatenating a string to a function

As you can see, you can easily concatenate a function with a string. To do something similar with a stored procedure in SQL, we will need an output variable in a stored procedure to concatenate the output variable with a string. Let’s take a look to the stored procedure:

The procedure is assigning the Hello Word string to an output parameter. You can use the out or output word to specify that the parameter is an output parameter.

The code may be simple, but calling the procedure to use the output parameter to be concatenated is a little bit more complex than a function:

As you can see, you need to declare a new variable named @message or any other name of your preference. When you call the stored procedure, you need to specify that it is an outer parameter. An advantage of the stored procedures is that you can have several parameters while in functions, you can return just one variable (scalar function) or one table (table-valued functions).

5. Invoke functions/procedures inside functions/Stored procedures in SQL

Can we invoke stored procedures inside a function?

Let’s take a look:

The function will invoke the HelloWorldprocedure created in the section 1.

If we invoke the function, we will have the following message:

Msg 557, Level 16, State 2, Line 65 Only functions and some extended stored procedures can be executed from within a function.

As you can see, you cannot call a function from a stored procedure. Can you call a function from a procedure?

Here it is the procedure:

If we invoke the stored procedure in SQL, we will be able to check if it works or not:

The result displayed is the following:

Reusability of stored procedure in SQL

Figure 5. A function inside a procedure

As you can see, you can invoke functions inside a stored procedure and you cannot invoke a stored procedure inside a function.

You can invoke a function inside a function. The following code shows a simple example:

We can call the function as usual:

Is it possible to call procedures inside other procedures?

Yes, you can. Here you have an example about it:

You can execute the procedure as usual:

Conclusions

Stored procedures in SQL are easier to create and functions have a more rigid structure and support less clauses and functionality. By the other hand, you can easily use the function results in T-SQL. We show how to concatenate a function with a string. Manipulating results from a stored procedure is more complex.

In a scalar function, you can return only one variable and in a stored procedure multiple variables. However, to call the output variables in a stored procedure, it is necessary to declare variables outside the procedure to invoke it.

In addition, you cannot invoke procedures within a function. By the other hand, in a procedure you can invoke functions and stored procedures.

Finally, it is important to mention some performance problems when we use functions. However, this disadvantage will be explained in a next article, Functions and stored procedures comparisons in SQL Server.

References

For more information, refer to these links:


Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
168 Views