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

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

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上学到的，非常有用。

在存储过程中，语句只被写入一次，从而减少了客户端和服务器之间的网络流量。 我们也可以避免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 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.