想想这样的情况，

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语句，用于执行特殊任务。

示例:如果我有一个Employee表

Employee ID  Name       Age  Mobile
---------------------------------------
001          Sidheswar  25   9938885469
002          Pritish    32   9178542436

首先，我正在检索Employee表:

Create Procedure Employee details
As
Begin
    Select * from Employee
End

在SQL Server上运行此过程:

Execute   Employee details

--- (Employee details is a user defined name, give a name as you want)

然后，我将值插入到Employee表中

Create Procedure employee_insert
    (@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
    Insert Into Employee
    Values (@EmployeeID, @Name, @Age, @Mobile)
End

在SQL Server上运行参数化过程:

Execute employee_insert 003,’xyz’,27,1234567890

  --(Parameter size must be same as declared column size)

示例:@Name Varchar(30)

在Employee表中，Name列的大小必须是varchar(30)。

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

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

存储过程将帮助您在服务器中编写代码。您可以传递参数并查找输出。

create procedure_name (para1 int,para2 decimal)
as
select * from TableName

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

语法参考

； 按此编号

可选整数，用于对同名过程进行分组。可以使用一个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的未来版本中删除。

序言:SQL92标准创建于1992年，并由Firebase DB推广。该标准引入了“存储过程”。

** 透传查询:一个字符串(通常通过编程连接)，计算为语法正确的SQL语句，通常在服务器层生成(在PHP、Python、PERL等语言中)。然后将这些语句传递到数据库。 **

** 触发器:设计用于响应数据库事件(通常是DML事件)而触发的一段代码，通常用于强制数据完整性。 **

解释什么是存储过程的最好方法是解释执行DB逻辑的传统方式(即不使用存储过程)。

创建系统的传统方式是使用“直通查询”，并且可能在DB中有触发器。 几乎所有不使用存储过程的人都会使用一个叫做"直通查询"的东西

随着存储过程的现代约定，触发器和“透传查询”一起成为传统。

存储过程的优点是:

They can be cached as the physical text of the Stored Procedure never changes. They have built in mechanisms against malicious SQL injection. Only the parameters need be checked for malicious SQL injection saving a lot of processor overhead. Most modern database engines actually compile Stored Procedures. They increase the degree of abstraction between tiers. They occur in the same process as the database allowing for greater optimisation and throughput. The entire workflow of the back end can be tested without client side code. (for example the Execute command in Transact SQL or the CALL command in MySQL). They can be used to enhance security because they can be leveraged to disallow the database to be accessed in a way that is inconsistent with how the system is designed to work. This is done through the database user permission mechanism. For example you can give users privileges only to EXECUTE Stored Procedures rather that SELECT, UPDATE etc privileges. No need for the DML layer associated with triggers. ** Using so much as one trigger, opens up a DML layer which is very processor intensive **

总之，在创建一个新的SQL数据库系统时，没有理由使用直通查询。

值得一提的是，在已经使用触发器或直通查询的遗留系统中使用存储过程是完全安全的;这意味着从遗留过程迁移到存储过程是非常容易的，而且这样的迁移不会使系统长时间停机。