create-a-stored-procedure.md

title	Create a Stored Procedure | Microsoft Docs
ms.custom
ms.date	03/16/2017
ms.prod	sql
ms.reviewer
ms.technology	stored-procedures
ms.topic	quickstart
helpviewer_keywords	new stored procedures stored procedures [SQL Server], creating creating stored procedures
ms.assetid	76e8a6ba-1381-4620-b356-4311e1331ca7
author	stevestein
ms.author	sstein
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Create a Stored Procedure

[!INCLUDEappliesto-ss-asdb-asdw-pdw-md]

This topic describes how to create a [!INCLUDEtsql] stored procedure by using [!INCLUDEssManStudioFull] and by using the [!INCLUDEtsql] CREATE PROCEDURE statement.

• Before you begin: Permissions

• To create a procedure, using: SQL Server Management Studio, Transact-SQL

Permissions

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 Studio

• Transact-SQL

Using SQL Server Management Studio

To create a procedure in Object Explorer

1. In Object Explorer, connect to an instance of [!INCLUDEssDE] and then expand that instance.

2. Expand Databases, expand the [!INCLUDEssSampleDBobject] 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	Value
   Author	Your name
   Create Date	Today's date
   Description	Returns employee data.
   Procedure_name	HumanResources.uspGetEmployeesTest
   @Param1	@LastName
   @Datatype_For_Param1	nvarchar(50)
   Default_Value_For_Param1	NULL
   @Param2	@FirstName
   @Datatype_For_Param2	nvarchar(50)
   Default_Value_For_Param2	NULL

6. Click OK.

7. 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. To run the procedure, in Object Explorer, right-click the stored procedure name HumanResources.uspGetEmployeesTest and select Execute Stored Procedure.

12. 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.

Warning

Validate all user input. Do not concatenate user input before you validate it. Never execute a command constructed from unvalidated user input.

Using Transact-SQL

To create a procedure in Query Editor

1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

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)