Functions vs stored procedures in SQL Server
Functions vs stored procedures in SQL Server
Figure 1. “Hello world” stored procedure result Now let’s try to do the same with a function: 12345678 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:
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.
Figure 3. Stored procedure in SQL to convert Celsius to Fahrenheit Let’s try to do the same with a function: 123456789 CREATE FUNCTION dbo.f_celsiustofahrenheit(@celcius real)RETURNS realAS BEGIN RETURN @celcius*1.8+32END You can call the function created in the following way: 123 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.
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: 123456 create procedure outputparam@paramout varchar(20) outasselect @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: 123456 declare @message varchar(20)exec outputparam @paramout=@message outselect @message as regardsselect 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).
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: 123456789 CREATE FUNCTION dbo.functioninsidefunction()RETURNS varchar(50)AS BEGIN RETURN dbo.helloworldfunction()END We can call the function as usual: 123 select dbo.functioninsidefunction() as regards Is it possible to call procedures inside other procedures? Yes, you can. Here you have an example about it: 12345 create procedure procedureinsideprocedureasexecute dbo.HelloWorldprocedure You can execute the procedure as usual: 123 exec dbo.procedureinsideprocedure
Author Recent Posts Daniel CalbimonteDaniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.
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 Latest posts by Daniel Calbimonte (see all) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022
SQLShack
SQL Server training EspañolFunctions vs stored procedures in SQL Server
February 14, 2017 by Daniel CalbimonteIntroduction
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/proceduresGetting 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: 12345 CREATE PROCEDURE HelloWorldprocedureASPRINT 'Hello World' Execute the code and then call the stored procedure in SQL: 123 exec HelloWorldprocedure If you execute the code, you will be able to see the “Hello World” message:Figure 1. “Hello world” stored procedure result Now let’s try to do the same with a function: 12345678 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:
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: 123456 exec HelloWorldprocedureexecute HelloWorldprocedureexecute dbo.HelloWorldprocedureHelloWorldprocedure 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: 123 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: 123 select dbo.helloworldfunction() as regards3 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: 123456 CREATE PROCEDURE CONVERTCELSIUSTOFAHRENHEIT@celsius realasselect @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: 123 exec CONVERTCELSIUSTOFAHRENHEIT 0 The result will be 32 °F:Figure 3. Stored procedure in SQL to convert Celsius to Fahrenheit Let’s try to do the same with a function: 123456789 CREATE FUNCTION dbo.f_celsiustofahrenheit(@celcius real)RETURNS realAS BEGIN RETURN @celcius*1.8+32END You can call the function created in the following way: 123 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: 123 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: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: 123456 create procedure outputparam@paramout varchar(20) outasselect @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: 123456 declare @message varchar(20)exec outputparam @paramout=@message outselect @message as regardsselect 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: 12345678910 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: 12345 create procedure functioninsideprocedureasselect dbo.helloworldfunction() If we invoke the stored procedure in SQL, we will be able to check if it works or not: 123 exec functioninsideprocedure The result displayed is the following: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: 123456789 CREATE FUNCTION dbo.functioninsidefunction()RETURNS varchar(50)AS BEGIN RETURN dbo.helloworldfunction()END We can call the function as usual: 123 select dbo.functioninsidefunction() as regards Is it possible to call procedures inside other procedures? Yes, you can. Here you have an example about it: 12345 create procedure procedureinsideprocedureasexecute dbo.HelloWorldprocedure You can execute the procedure as usual: 123 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: CREATE FUNCTION (Transact-SQL) CREATE PROCEDURE (Transact-SQL) CONCAT (Transact-SQL)Author Recent Posts Daniel CalbimonteDaniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.
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 Latest posts by Daniel Calbimonte (see all) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022