创建存储过程Create a Stored Procedure

适用对象:yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

本主题介绍了如何通过使用 Transact-SQLTransact-SQLSQL Server Management StudioSQL Server Management Studio CREATE PROCEDURE 语句来创建 Transact-SQLTransact-SQL 存储过程。This topic describes how to create a Transact-SQLTransact-SQL stored procedure by using SQL Server Management StudioSQL Server Management Studio and by using the Transact-SQLTransact-SQL CREATE PROCEDURE statement.

PermissionsPermissions

需要在数据库中有 CREATE PROCEDURE 权限,对在其中创建过程的架构有 ALTER 权限。Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

如何创建存储过程How to Create a Stored Procedure

您可以使用以下项之一:You can use one of the following:

使用 SQL Server Management StudioUsing SQL Server Management Studio

在对象资源管理器中创建过程To create a procedure in Object Explorer

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.

  2. 依次展开 “数据库”AdventureWorks2012AdventureWorks2012 数据库和 “可编程性”Expand Databases, expand the AdventureWorks2012AdventureWorks2012 database, and then expand Programmability.

  3. 右键单击“存储过程”,再单击“新建存储过程”。Right-click Stored Procedures, and then click New Stored Procedure.

  4. “查询” 菜单上,单击 “指定模板参数的值”On the Query menu, click Specify Values for Template Parameters.

  5. “指定模板参数的值” 对话框中,输入下列所示的参数值。In the Specify Values for Template Parameters dialog box, enter the following values for the parameters shown.

    参数Parameter ReplTest1Value
    作者Author 您的姓名Your name
    创建日期Create Date 今天的日期Today's date
    描述Description 返回雇员数据。Returns employee data.
    Procedure_nameProcedure_name HumanResources.uspGetEmployeesTestHumanResources.uspGetEmployeesTest
    @Param1 @LastName
    @Datatype_For_Param1 nvarchar(50)nvarchar(50)
    Default_Value_For_Param1Default_Value_For_Param1 NULLNULL
    @Param2 @FirstName
    @Datatype_For_Param2 nvarchar(50)nvarchar(50)
    Default_Value_For_Param2Default_Value_For_Param2 NULLNULL
  6. 单击“确定” 。Click OK.

  7. “查询编辑器” 中,使用以下语句替换 SELECT 语句:In the Query Editor, replace the SELECT statement with the following statement:

    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
        AND EndDate IS NULL;  
    
  8. 若要测试语法,请在 “查询” 菜单上,单击 “分析”To test the syntax, on the Query menu, click Parse. 如果返回错误消息,则请将这些语句与上述信息进行比较,并视需要进行更正。If an error message is returned, compare the statements with the information above and correct as needed.

  9. 若要创建该过程,请在 “查询” 菜单上单击 “执行”To create the procedure, from the Query menu, click Execute. 该过程作为数据库中的对象创建。The procedure is created as an object in the database.

  10. 若要查看在对象资源管理器中列出的过程,请右键单击“存储过程”,然后选择“刷新”。To see the procedure listed in Object Explorer, right-click Stored Procedures and select Refresh.

  11. 若要运行该过程,请在对象资源管理器中右键单击存储过程名称 HumanResources.uspGetEmployeesTest,然后选择“执行存储过程”。To run the procedure, in Object Explorer, right-click the stored procedure name HumanResources.uspGetEmployeesTest and select Execute Stored Procedure.

  12. 在“执行过程”窗口中,输入 Margheim 作为参数 @LastName 的值,并输入值 Diane 作为参数 @FirstName 的值。In the Execute Procedure window, enter Margheim as the value for the parameter @LastName and enter the value Diane as the value for the parameter @FirstName.

警告

验证所有用户的输入。Validate all user input. 验证前请勿连接用户输入。Do not concatenate user input before you validate it. 绝对不要执行根据尚未验证的用户输入构造的命令。Never execute a command constructed from unvalidated user input.

使用 Transact-SQLUsing Transact-SQL

在查询编辑器中创建过程To create a procedure in Query Editor

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. “文件” 菜单中,单击 “新建查询”From the File menu, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 该示例将使用其他过程名称创建与上述相同的存储过程。This example creates the same stored procedure as above using a different procedure name.

    USE AdventureWorks2012;  
    GO  
    CREATE PROCEDURE HumanResources.uspGetEmployeesTest2   
        @LastName nvarchar(50),   
        @FirstName nvarchar(50)   
    AS   
    
        SET NOCOUNT ON;  
        SELECT FirstName, LastName, Department  
        FROM HumanResources.vEmployeeDepartmentHistory  
        WHERE FirstName = @FirstName AND LastName = @LastName  
        AND EndDate IS NULL;  
    GO  
    
    
  4. 若要运行该过程,请将以下示例复制并粘贴到一个新的查询窗口中,然后单击 “执行”To run the procedure, copy and paste the following example into a new query window and click Execute. 请注意,将显示指定参数值的不同方法。Notice that different methods of specifying the parameter values are shown.

    EXECUTE HumanResources.uspGetEmployeesTest2 N'Ackerman', N'Pilar';  
    -- Or  
    EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar';  
    GO  
    -- Or  
    EXECUTE HumanResources.uspGetEmployeesTest2 @FirstName = N'Pilar', @LastName = N'Ackerman';  
    GO  
    
    

另请参阅See Also

CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)