存储过程不过是编译成单个执行计划的一组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

在存储过程中，语句只被写入一次，从而减少了客户端和服务器之间的网络流量。 我们也可以避免Sql注入攻击。

Incase if you are using a third party program in your application for processing payments, here database should only expose the information it needed and activity that this third party has been authorized, by this we can achieve data confidentiality by setting permissions using Stored Procedures. The updation of table should only done to the table it is targeting but it shouldn't update any other table, by which we can achieve data integrity using transaction processing and error handling. If you want to return one or more items with a data type then it is better to use an output parameter. In Stored Procedures, we use an output parameter for anything that needs to be returned. If you want to return only one item with only an integer data type then better use a return value. Actually the return value is only to inform success or failure of the Stored Procedure.

存储过程不过是编译成单个执行计划的一组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

存储过程用于检索数据、修改数据和删除数据库表中的数据。你不需要每次在SQL数据库中插入、更新或删除数据时都写一个完整的SQL命令。

对于简单的,

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

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

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

一般来说，存储过程是一个“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注入攻击。