SQL Server中的功能与存储过程

介绍 (Introduction)

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

通常，DBA倾向于使用SQL中的存储过程，而不是SQL Server中的函数。 这是一个好习惯吗？

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.

在本文中，我们将讲授如何在SQL Server中创建存储过程和函数，并展示每种方法的优缺点。 在我们的示例中，我们将使用标量用户定义函数（也称为UDF）。 将来我们将展示一些表值函数。 这里不介绍CLR功能。

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.在SQL和函数的存储过程中创建一个Hello World (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.

让我们在存储过程中创建一个简单的“ Hello world”，并创建一个函数来验证哪个更易于创建。

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

我们将首先使用SSMS中的print语句创建一个简单的存储过程：

 
CREATE PROCEDURE HelloWorldprocedure
AS
PRINT 'Hello World'
 

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

执行代码，然后在SQL中调用存储过程：

 
exec HelloWorldprocedure
 

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

如果执行代码，您将能够看到“ Hello World”消息：

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

现在，让我们尝试对一个函数执行相同的操作：

 
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.

如果您比较代码，则该功能需要更多代码才能执行同一操作。 在函数中，BEGIN和END块是必需的，而如果存储过程仅一行，则不需要它们。 在函数中，必须使用RETURNS和RETURN参数，而在存储过程中则没有必要。

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.在SQL中调用存储过程与调用函数 (2. Invoking a stored procedure in SQL vs invoking a function)

You can invoke a stored procedure in different ways:

您可以通过不同的方式调用存储过程：

 
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.

您可以使用exec或execute来调用，甚至可以不使用execute语句来调用存储过程。 您不一定需要指定架构名称。

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:

让我们调用一个没有模式的函数：

 
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

消息195，级别15，状态10，第20行'helloworldfunction'不是公认的内置函数名称

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

如您所见，架构名称是调用函数所必需的：

 
select dbo.helloworldfunction() as regards
 

3.在SQL和函数中的存储过程中使用变量 (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:

我们将使用存储的过程和函数将摄氏温度转换为华氏温度，以查看差异。 让我们从存储过程开始：

 
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.

摄氏温度是输入参数，我们正在执行select语句中的计算以转换为华氏度。

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

如果调用存储过程，我们将验证转换为0°C的结果：

 
exec CONVERTCELSIUSTOFAHRENHEIT 0 
 

The result will be 32 °F:

结果将是32°F：

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

让我们尝试使用一个函数做同样的事情：

 
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:

您可以通过以下方式调用创建的函数：

 
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.

我们正在将0°C转换为°F。 如您所见，两种情况下的代码都很简单。

4.可重用性 (4. Reusability)

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

函数的主要优点是可以在代码中重用。 例如，您可以执行以下操作：

 
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:

在此示例中，我们将示例1的函数与字符串连接在一起。 结果如下：

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:

如您所见，您可以轻松地将函数与字符串连接起来。 为了在SQL中对存储过程执行类似的操作，我们将需要在存储过程中使用输出变量来将输出变量与字符串连接起来。 让我们看一下存储过程：

 
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.

该过程是将Hello Word字符串分配给输出参数。 您可以使用out或输出字来指定该参数是输出参数。

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:

代码可能很简单，但是调用过程以使用输出参数进行连接要比函数复杂一点：

 
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).

如您所见，您需要声明一个名为@message的新变量或您的首选项的任何其他名称。 调用存储过程时，需要指定它是外部参数。 存储过程的一个优点是，在函数中可以有多个参数，而只能返回一个变量（标量函数）或一个表（表值函数）。

5.在SQL中调用函数/函数内部的过程/存储过程 (5. Invoke functions/procedures inside functions/Stored procedures in SQL)

Can we invoke stored procedures inside a function?

我们可以在函数内部调用存储过程吗？

Let’s take a look:

让我们来看看：

 
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.

该函数将调用在第1节中创建的HelloWorldprocedure。

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.

消息557，级别16，状态2，第65行只有一个函数和某些扩展的存储过程可以从一个函数中执行。

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:

这是过程：

 
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:

如果我们在SQL中调用存储过程，我们将能够检查它是否有效：

 
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:

您可以在函数内部调用函数。 以下代码显示了一个简单的示例：

 
CREATE FUNCTION dbo.functioninsidefunction()
RETURNS varchar(50)
AS 
BEGIN
	
	RETURN  dbo.helloworldfunction()
END
 

We can call the function as usual:

我们可以像往常一样调用该函数：

 
select dbo.functioninsidefunction() as regards
 

Is it possible to call procedures inside other procedures?

是否可以在其他过程中调用过程？

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

是的你可以。 这里有一个例子：

 
create procedure procedureinsideprocedure
as
execute dbo.HelloWorldprocedure
 

You can execute the procedure as usual:

您可以照常执行该过程：

 
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.

SQL中的存储过程更易于创建，并且函数具有更严格的结构，并且支持较少的子句和功能。 另一方面，您可以轻松地在T-SQL中使用函数结果。 我们展示了如何用字符串连接一个函数。 从存储过程中操作结果更为复杂。

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.

最后，重要的是在使用函数时要提到一些性能问题。 但是，此缺点将在下一篇文章SQL Server中的函数和存储过程比较中进行说明。

参考资料 (References)

For more information, refer to these links:

有关更多信息，请参考以下链接：

• CREATE FUNCTION (Transact-SQL) 创建功能（Transact-SQL）
• CREATE PROCEDURE (Transact-SQL) 创建过程（Transact-SQL）
• CONCAT (Transact-SQL) CONCAT（Transact-SQL）

翻译自: https://www.sqlshack.com/functions-vs-stored-procedures-sql-server/