存储过程是一组已创建并存储在数据库中的SQL语句。存储过程将接受输入参数，以便多个使用不同输入数据的客户端可以通过网络使用单个过程。存储过程可以减少网络流量并提高性能。如果我们修改一个存储过程，所有的客户端都将得到更新后的存储过程。

创建存储过程的示例

CREATE PROCEDURE test_display
AS
    SELECT FirstName, LastName
    FROM tb_test;

EXEC test_display;

使用存储过程的优点

A stored procedure allows modular programming. You can create the procedure once, store it in the database, and call it any number of times in your program. A stored procedure allows faster execution. If the operation requires a large amount of SQL code that is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in a memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use, resulting in much faster execution times. A stored procedure can reduce network traffic. An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network. Stored procedures provide better security to your data Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly. In SQL Server we have different types of stored procedures: System stored procedures User-defined stored procedures Extended stored Procedures System-stored procedures are stored in the master database and these start with a sp_ prefix. These procedures can be used to perform a variety of tasks to support SQL Server functions for external application calls in the system tables Example: sp_helptext [StoredProcedure_Name] User-defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use the sp_ prefix because if we use the sp_ prefix first, it will check the master database, and then it comes to user defined database. Extended stored procedures are the procedures that call functions from DLL files. Nowadays, extended stored procedures are deprecated for the reason it would be better to avoid using extended stored procedures.

“什么是存储过程”已经在这里的其他帖子中回答过了。我将要发布的是一种不太为人所知的使用存储过程的方法。它对存储过程进行分组或对存储过程进行编号。

语法参考

； 按此编号

可选整数，用于对同名过程进行分组。可以使用一个DROP PROCEDURE语句将这些分组的过程放到一起

例子

CREATE Procedure FirstTest 
(
    @InputA INT
)
AS 
BEGIN
    SELECT 'A' + CONVERT(VARCHAR(10),@InputA)
END
GO

CREATE Procedure FirstTest;2
(
    @InputA INT,
    @InputB INT
)
AS 
BEGIN
    SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO

Use

exec FirstTest 10
exec FirstTest;2 20,30

结果

另一个尝试

CREATE Procedure SecondTest;2
(
     @InputA INT,
    @InputB INT
)
AS 
BEGIN
    SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO

结果

Msg 2730，级别11，状态1，程序SecondTest，行1[批量启动行3] 无法创建组号为2的过程“SecondTest”，因为数据库中当前不存在同名且组号为1的过程。 必须先执行CREATE PROCEDURE 'SecondTest';

引用:

使用数字语法创建过程 在SQL Server中编号的存储过程 对存储过程进行分组- sqlmag .

谨慎

对过程进行分组后，就不能单独删除它们了。 此功能可能会在Microsoft SQL Server的未来版本中删除。

对于简单的,

存储过程是存储程序，存储在数据库中的程序或函数。

每个存储的程序都包含一个由SQL语句组成的主体。该语句可以是由几个用分号(;)分隔的语句组成的复合语句。

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

SQL Server中的存储过程可以接受输入参数并返回多个输出参数值;在SQL Server中，存储过程编写语句在数据库中执行操作，并将状态值返回给调用过程或批处理。

在SQL Server中使用存储过程的好处

它们允许模块化编程。 它们允许更快的执行。 它们可以减少网络流量。 它们可以用作一种安全机制。

下面是一个存储过程的示例，它接受参数，执行查询并返回结果。具体来说，存储过程接受BusinessEntityID作为参数，并使用它来匹配HumanResources的主键。Employee表返回所请求的员工。

> create procedure HumanResources.uspFindEmployee    `*<<<---Store procedure name`*
@businessEntityID                                     `<<<----parameter`
as
begin
SET NOCOUNT ON;
Select businessEntityId,              <<<----select statement to return one employee row
NationalIdNumber,
LoginID,
JobTitle,
HireData,
From HumanResources.Employee
where businessEntityId =@businessEntityId     <<<---parameter used as criteria
end

这是我从essential.com上学到的，非常有用。

想想这样的情况，

You have a database with data. There are a number of different applications needed to access that central database, and in the future some new applications too. If you are going to insert the inline database queries to access the central database, inside each application's code individually, then probably you have to duplicate the same query again and again inside different applications' code. In that kind of a situation, you can use stored procedures (SPs). With stored procedures, you are writing number of common queries (procedures) and store them with the central database. Now the duplication of work will never happen as before and the data access and the maintenance will be done centrally.

注意:

In the above situation, you may wonder "Why cannot we introduce a central data access server to interact with all the applications? Yes. That will be a possible alternative. But, The main advantage with SPs over that approach is, unlike your data-access-code with inline queries, SPs are pre-compiled statements, so they will execute faster. And communication costs (over networks) will be at a minimum. Opposite to that, SPs will add some more load to the database server. If that would be a concern according to the situation, a centralized data access server with inline queries will be a better choice.

存储过程主要用于在数据库上执行某些任务。例如

从数据上的一些业务逻辑获取数据库结果集。 在一次调用中执行多个数据库操作。 用于将数据从一个表迁移到另一个表。 可用于其他编程语言，如Java。