Wednesday, January 16

Stored Procedure-SQL

Stored Procedure

stored procedure is a group of Transact-SQL statements compiled into a single execution plan. Or, it can be defined as pre-compiled database queries that improve the security, efficiency and usability of database client/server applications. Its a piece of SQL code that is physically stored in the database and it can be executed (like query) again and again for different purposes. It can be as simple as simple SQL selection query and as complex as doing multiple tasks (insertion, selection, joins, deletion) at the same time. 

How can it add to the security? By encapsulation of code, i.e the user just calls the procedure and executes it, the full code is not displayed.

Syntax


CREATE PROCEDURE Procedure_Name       

 --Instead of Procedure you can also use Proc 

/*  

( @parameter1 datatype = default value,
      @parameter2 datatype OUTPUT
 ) 
*/

As

Begin

-- YOUR QUERY

End

For Example, Consider that i already created a table called EMPLOYEE, which has Empid, Empname, Salary and Place as columns. Now i want to insert values into these columns using Stored Procedure, So that i don't need to use INSERT statements everytime. See the below Stored Procedure.

Stored Procedure for Inserting values into Employee table.


CREATE PROCEDURE [dbo].[sp_insertvalues]       

     (
       @Empid int,
     @Empname varchar(50),
@Salary money,
@Place varchar(50)
     )
As
Begin
INSERT into Employee (Empid, Empname, Salary, Place) values (@Empid, @Empname, @Salary,@Place)
End

Now if you want to  modify and insert values into some more columns, write the  query as shown below.


ALTER PROCEDURE [dbo].[sp_insertvalues]       

     (
       @Empid int,
     @Empname varchar(50),
@Salary money,
@Place varchar(50)
@Gender Varchar(5)
@Address Varchar(100)
     )
As
Begin
INSERT into Employee (Empid, Empname, Salary, Place, Gender, Addressvalues (@Empid, @Empname, @Salary, @Place, @Gender, @Address )
End

To call/Execute this Stored Procedure, see the query below.

EXEC sp_insertvalues 12, 'John', 20000, 'ZZZ', 'M', 'YYY'

** These values will be inserted into Employee table

** Related Topics on Stored Procrdures