SQL Stored Procedures for SQL Server 存储过程

SQL Stored Procedures for SQL Server

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

我理解类似于编程里的函数，每次使用或者重复使用的话就进行调用。

语法

创建存储过程

1.

CREATE PROCEDURE precedure_name

AS

Sql_statement

GO;

执行存储过程

2.

EXEC procedure_name;

Eg:

The following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:

3.

CREATE PROCEDURE SelectAllCustomers

AS

SELECT * FROM Customers

GO;

4.

EXEC SelectAllCUstomers;

5.Stored Procedure With One Parameter

creates a stored procedure that selects Customers from a particular City from the "Customers" table:

6.

CREATE PROCEDURE  SlectAllCustomers @City nvarchar(30)

AS

SELECT * FROM Customers WHERE City= @City

GO;

 

6.

EXEC SlectAllCustomers @City='London'

 

7.

Stored Procedure With Multiple Parameters

8.

CRREATE PROCEDURE SelectAllCustomers @City nvarchar(30),@PostalCode nvarchar(10)

AS

SELECT * FROM WHERE City=@City AND PostcalCode=@PostalCode

GO;

 

9.

EXEC SelectAllCustomers @City='London',@PostcalCode='WA1 1DP';