How To Write Stored Procedure in SQL Server


This article describes how to create a Transact-SQL stored procedure by using SQL Server Management Studio and by using the Transact-SQL CREATE PROCEDURE statement. You can read Advantages and Drawbacks of Using Stored Procedures here.

First we will create database for demonstratioin :

CREATE DATABASE TestDatabase;

Create table Employee using below script :

IF OBJECT_ID ('dbo.Employee') IS NOT NULL
    DROP TABLE dbo.Employee
GO

CREATE TABLE dbo.Employee
    (
    EmployeeId NUMERIC (18) NOT NULL,
    FirstName  NVARCHAR (50),
    LastName   NVARCHAR (50),
    Email      NVARCHAR (50),
    CONSTRAINT PK_Employee PRIMARY KEY (EmployeeId)
    )
GO

We will insert some test records in table Employee :

INSERT INTO dbo.Employee (EmployeeId, FirstName, LastName, Email)
VALUES (1, 'First Name 1', 'Last Name 1', 'employee1@test.com')
GO

INSERT INTO dbo.Employee (EmployeeId, FirstName, LastName, Email)
VALUES (2, 'First Name 2', 'Last Name 2', 'employee2@test.com')
GO

INSERT INTO dbo.Employee (EmployeeId, FirstName, LastName, Email)
VALUES (3, 'First Name 3', 'Last Name 3', 'employee3@test.com')
GO

INSERT INTO dbo.Employee (EmployeeId, FirstName, LastName, Email)
VALUES (4, 'First Name 4', 'Last Name 4', 'employee4@test.com')
GO

INSERT INTO dbo.Employee (EmployeeId, FirstName, LastName, Email)
VALUES (5, 'First Name 5', 'Last Name 5', 'employee5@test.com')
GO

You can use one of the following :

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 expand that instance.
  2. Expand Databases, expand the TestDatabase 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 Description of Stored Procedure
    Procedure_name Your Stored Procedre Name
    @Param1 Parameter 1 For Stored Procedure
    @Datatype_For_Param1 Datatype For Parameter 1
    @Param2 Parameter 2 For Stored Procedure
    @Datatype_For_Param2 Datatype For Parameter 2
  6. Here, I am using one parameter EmployeeId. Click OK.
  7. In the Query Editor, replace the SELECT statement with the following statement :
    SELECT * FROM Employee WHERE EmployeeId = @EmployeeId
    
  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 and select Execute Stored Procedure.
  12. In the Execute Procedure window, enter 1 as the value for the parameter @EmployeeId.

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 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 TestDatabase;
    GO
    CREATE PROCEDURE GetEmployeeDetails 
        @EmployeeId int  
    AS 
    
        SET NOCOUNT ON;
        SELECT * FROM Employee WHERE EmployeeId = @EmployeeId
    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 GetEmployeeDetails 1
    -- Or
    EXEC GetEmployeeDetails 1
    
Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s