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:
|
1 2 3 4 5 |
CREATE PROCEDURE HelloWorldprocedure AS PRINT 'Hello World' |
Execute the code and then call the stored procedure in SQL:
|
1 2 3 |
exec HelloWorldprocedure |
If you execute the code, you will be able to see the “Hello World” message:
Now let’s try to do the same with a function:
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION dbo.helloworldfunction() RETURNS varchar(20) AS BEGIN RETURN 'Hello world' END |
We can call the function using a select:
The function will return the following message:
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:
|
1 2 3 4 5 6 |
exec HelloWorldprocedure execute HelloWorldprocedure execute dbo.HelloWorldprocedure HelloWorldprocedure |
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:
|
1 2 3 |
select helloworldfunction() as regards |
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:
|
1 2 3 |
select dbo.helloworldfunction() as regards |
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:
|
1 2 3 4 5 6 |
CREATE PROCEDURE CONVERTCELSIUSTOFAHRENHEIT @celsius real as select @celsius*1.8+32 as Fahrenheit |
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:
|
1 2 3 |
exec CONVERTCELSIUSTOFAHRENHEIT 0 |
The result will be 32 °F:
Let’s try to do the same with a function:
|
1 2 3 4 5 6 7 8 9 |
CREATE FUNCTION dbo.f_celsiustofahrenheit(@celcius real) RETURNS real AS BEGIN RETURN @celcius*1.8+32 END |
You can call the function created in the following way:
|
1 2 3 |
select dbo.f_celsiustofahrenheit(0) as fahrenheit |
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:
|
1 2 3 |
select CONCAT(dbo.helloworldfunction(),', welcome to sqlshack') Regards |
In this example, we are concatenating the function of the example 1 with a string. The result is the following:
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:
|
1 2 3 4 5 6 |
create procedure outputparam @paramout varchar(20) out as select @paramout='Hello world' |
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:
|
1 2 3 4 5 6 |
declare @message varchar(20) exec outputparam @paramout=@message out select @message as regards select CONCAT(@message,', welcome to sqlshack') |
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:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE FUNCTION dbo.procedureinsidefunction() RETURNS varchar(22) AS BEGIN execute HelloWorldprocedure Declare @hellovar varchar(22)=', welcome to sqlshack' RETURN @hellovar END |
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:
|
1 2 3 4 5 |
create procedure functioninsideprocedure as select dbo.helloworldfunction() |
If we invoke the stored procedure in SQL, we will be able to check if it works or not:
|
1 2 3 |
exec functioninsideprocedure |
The result displayed is the following:
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:
|
1 2 3 4 5 6 7 8 9 |
CREATE FUNCTION dbo.functioninsidefunction() RETURNS varchar(50) AS BEGIN RETURN dbo.helloworldfunction() END |
We can call the function as usual:
|
1 2 3 |
select dbo.functioninsidefunction() as regards |
Is it possible to call procedures inside other procedures?
Yes, you can. Here you have an example about it:
|
1 2 3 4 5 |
create procedure procedureinsideprocedure as execute dbo.HelloWorldprocedure |
You can execute the procedure as usual:
|
1 2 3 |
exec dbo.procedureinsideprocedure |
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:
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023