想想这样的情况，

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.

对于简单的,

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

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

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

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

语法参考

； 按此编号

可选整数，用于对同名过程进行分组。可以使用一个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函数”。他们有:

-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID

这是一个以T-SQL为重点的示例。存储过程可以执行大多数SQL语句，返回标量值和基于表的值，并且被认为更安全，因为它们可以防止SQL注入攻击。

在DBMS中，存储过程是一组具有指定名称的SQL语句，这些SQL语句以编译后的形式存储在数据库中，这样就可以由许多程序共享。

存储过程的使用在

提供对数据的受控访问(最终用户只能输入或更改数据，但不能编写过程) 确保数据完整性(数据将以一致的方式输入)和 提高工作效率(存储过程的语句只需编写一次)

存储过程不过是编译成单个执行计划的一组SQL语句。

创建一次，调用n次 减少网络流量

示例:创建存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
      @EmployeeID int = 0
AS
BEGIN
      SET NOCOUNT ON;

      SELECT FirstName, LastName, BirthDate, City, Country
      FROM Employees 
      WHERE EmployeeID = @EmployeeID
END
GO

更改或修改存储过程:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE GetEmployee
      @EmployeeID int = 0
AS
BEGIN
    SET NOCOUNT ON;

    SELECT FirstName, LastName, BirthDate, City, Country
    FROM Employees 
    WHERE EmployeeID = @EmployeeID
END
GO

删除存储过程:

DROP PROCEDURE GetEmployee