Skip to main content

SQL Stored Procedure

        Stored Procedure is group of SQL statement that may stored on database server.  It can be response to any number of application.  The stored procedure are used to avoid duplicates of sql statement in application.we can also pass  parameter to stored procedure and execute stored procedure by remote call. 
Syntax
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
//Declaration Part
AS
BEGIN
// SQL STATEMENT BLOCK;
END
GO
In declare part we can declare variable. In SQL STATEMENT BLOCK we can use any sql statement INSERT, SELECT, UPDATE, DELETE etc.
In application will call procedure by EXEC command along with procedure name, ie Exec ProcedureName(parameterlist). 
Example Stored Procedure
Stored Procedure with Insert Statement
CREATE PROCEDURE insertstudent_tb
-- Add the parameters for the stored procedure here
      @Regno numeric(18,0),
      @Name text,      
      @Gender nvarchar(50),
      @dob date,
      @Email_id text,
       AS
BEGIN
 //Insert statements for procedure here
insert into student1_tb ( 
      Regno ,Name,Gender ,DOB ,Email_id )       
values( @Regno ,@Name, @Gender ,@dob ,@Email_id );
END
The  @Regno ,@Name, @Gender ,@dob ,@Email_id are variables and are used in insert statement.

SELECT Statement in Stored Procedure.
CREATE PROCEDURE viewstudent_tb
AS
BEGIN
 SELECT *FROM student_tb;
END

ALTER Stored Procedure.
        Instead of CREATE keyword we use ALTER keyword along procedure name that we want to modify.
ALTER PROCEDURE insertstudent_tb
-- Add the parameters for the stored procedure here
      @Regno numeric(18,0),
      @Name text,      
      @Gender nvarchar(50),
      @dob date,
      @Email_id text,
       AS
BEGIN
 //Insert statements for procedure here
insert into student_tb ( 
      Regno ,Name,Gender ,DOB ,Email_id )       
values( @Regno ,@Name, @Gender ,@dob ,@Email_id );
END

Execute Stored Procedure
EXEC Procedure_name;
Exec viewstudent_tb;

Exec  insertstudent_tb @Regno=1,@Name text='William', @Gender='male'
 @dob date='11.11.89', @Email_id='William1989@gmail.com


Thank You. Like, Share and Subscribe 👍 Followlect.com

Comments