Introduction:

Here we will explain how to create store procedure in SQL server or how to use store procedure in SQL server or How to pass parameters in SQL Server or How to insert record by using store procedure and how to drop procedure in SQL server with Example.

Description:

The SQL stored procedure or simply a procedure is a group of SQL statements which performs one or more specific tasks. It is just like procedures in other programming languages.

The procedure contains two sections.

  • Header: The header consists the name of the procedure and the parameters or variables passed to the procedure.
  • Body: The body consists the declaration section, execution section and exception section similar to general SQL statements.

 

  • Procedures: Parameters

When you need to create a procedure, you have to define parameters .There is three ways to pass parameters in procedure:

  1. IN parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be changed by the procedure or the function.
  2. OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be changed by the procedure or function.
  3. INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be changed by the procedure or function.

 

  • SQL Create Procedure

Syntax for creating procedure:

 

CREATE PROCEDURE <Procedure_Name> 
(
     
      <@Param2>
      <@Param1>,
}
AS
BEGIN
      SELECT <column1>, <column1> from Tablename
END

 

  • SQL Procedure example

In this example, we are going to insert record in employee table. So you need to create employee table first.

Table creation:

CREATE TABLE [dbo].[employee](
      [Emp_ID] [bigint] primary key identity(1,1) Not NULL,
      [Emp_FirstName] [varchar](255) NULL,
      [Emp_LastName] [varchar](255) NULL,
      [Emp_City] [varchar](255) NULL,
      [Emp_Email] [varchar](200) NULL,
 
) 

Now write the procedure code to insert record in employee table.

Procedure Code:

CREATE PROCEDURE INSERT_EMPLOYEE    
(
@Emp_ID BIGINT,
@Emp_FirstName varchar(255) ,
@Emp_LastName varchar(255) ,
@Emp_City varchar(255) ,
@Emp_Email varchar(200)
)
AS
BEGIN   
INSERT INTO [employee]
VALUES(@Emp_ID,@Emp_FirstName,@Emp_LastName,@Emp_City,@Emp_Email);   
END; 

 

  • SQL program to call procedure

Let's see the code to call above created procedure.

 EXEC INSERT_EMPLOYEE 1,'aditya','patel','allahabad','adityasingh06630@gmail.com'

Now, see the "employee" table, you will see one record is inserted.


How to create store procedure in Sql Server


  • SQL Drop Procedure

Syntax for drop procedure

DROP PROCEDURE procedure_name;
  • Example of drop procedure

DROP PROCEDURE INSERT_EMPLOYEE;


i hope this will be helpful for you

0 comments


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like