Stored Procedures in Sql

Stored Procedures in Sql

"A Stored Procedures is a group of SQL Statements that has been Created and Stored in the database."

............................................................................................................

Advantage :-

• Better performance
• Less Traffic
• Security
• Integrity
• Code-Reuseblity

Disadvantage :-

• Work with Same Database

............................................................................................................

Design a database like this image :-

---------------------------------------------------------------------------------------------------------

Create a Table "Student"

Create table Student         (      RollNo int primary key,      Name nvarchar(50),      MobileNo nvarchar(50),      dob nvarchar(50)       )

-------------------------------------------------------------------------------------------------------

Create a Stored Procedures..........................

A Single Stored Procedures for multiple Query Insert,Update,Delete, & Select .. i.e. SP_Query

-------------------------------------------------------------------------------------------------------------------------

Create proc SP_Query 

      @op nvarchar(50)='g',

      @RollNo int=0,

      @Name nvarchar(50)=null,

      @MobileNo nvarchar(50)=null,

      @dob nvarchar(50)=''

as

begin

      if @op='insert'

begin

      insert into Student values(@RollNo,@Name,@MobileNo,@dob)

end

      if @op='update'

begin

      update Student set 

                 Name=@Name,

                 MobileNo=@MobileNo,

                 dob=@dob

       where RollNo=@RollNo

end

       if @op='delete'

begin

       delete from Student where RollNo=@RollNo

end

       if @op='select'

begin

        select * from Student where RollNo=@RollNo

end

else

begin

        select * from Student

end

end

--------------------------------------------------------------------------------------------------------------------------

Execute a Stored Procedures..........................

1.  executing procedure for insert the data in Student table

exec SP_Query  'insert',3,'Mr. VICKY','09654388184','02-03-1993'

2. executing  procedure for updating data in Student table

  exec SP_Query  'update',2,'Mr. Vijay','08826439190','11-Oct-1993'

3. executing  procedure for select data in Student table

    exec SP_Query  'select',1

4. executing  procedure for delete data in Student table  

   exec SP_Query  'delete',4

5. executing  default procedure for  Student table  

      exec SP_Query 

-------------------------------------------------------------------------------