Create a stored procedure

  • Article
  • ii minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform System (PDW)

This topic describes how to create a Transact-SQL stored process by using SQL Server Management Studio and by using the Transact-SQL CREATE PROCEDURE statement.

Permissions

Requires CREATE Process permission in the database and Modify permission on the schema in which the procedure is beingness created.

How to create a stored process

Yous tin can use one of the post-obit:

  • 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 Database Engine and then aggrandize that case.

  2. Expand Databases, expand the AdventureWorks2012 database, and and so expand Programmability.

  3. Right-click Stored Procedures, and then click New Stored Procedure.

  4. On the Query bill of fare, 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 engagement
    Clarification 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 Zip

  6. Click OK.

  7. In the Query Editor, replace the SELECT statement with the following argument:

                      SELECT FirstName, LastName, Section   FROM HumanResources.vEmployeeDepartmentHistory   WHERE FirstName = @FirstName AND LastName = @LastName       AND EndDate IS Aught;

  8. To test the syntax, on the Query menu, click Parse. If an error message is returned, compare the statements with the data above and correct as needed.

  9. To create the process, from the Query carte, click Execute. The process is created as an object in the database.

  10. To run across the procedure listed in Object Explorer, correct-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 Process.

  12. In the Execute Process window, enter Margheim as the value for the parameter @LastName and enter the value Diane equally the value for the parameter @FirstName.

Alert

Validate all user input. Do non concatenate user input before you validate information technology. 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 Database Engine.

  2. From the File bill of fare, 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 to a higher place using a different process proper name.

                      USE AdventureWorks2012;   Get   CREATE Process HumanResources.uspGetEmployeesTest2        @LastName nvarchar(50),        @FirstName nvarchar(50)    Equally         SET NOCOUNT ON;       SELECT FirstName, LastName, Section       FROM HumanResources.vEmployeeDepartmentHistory       WHERE FirstName = @FirstName AND LastName = @LastName       AND EndDate IS Cipher;   GO

  4. To run the procedure, copy and paste the following case into a new query window and click Execute. Notice that dissimilar methods of specifying the parameter values are shown.

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

Next steps

CREATE PROCEDURE (Transact-SQL)