Database - STORED PROCEDURE

Stored procedure is a set of pre-compiled queries.The queries are created and

 compiled and stored.when a stored procedure is called,it is executed directly

 without compiling it.Stored procedure gives re-usability.

'A simple stored procedure'

 create procedure sp_ps

As

select * from student

exec sp_ps   -executes the procedure

sp_helptext sp_ps   -gives the queries of the stored procedure.

drop procedure sp_ps

'Stored procedure with parameter using Insert statement'

   - we can create an Stored procedure with parameter.

create table store(stname char(4),stnm varchar(40),staddr varchar(40),city varchar(20),

state char(2),zip char(5))

select * from store

create procedure insert_stores(

@stid char(4),@stnm varchar(40),@staddr varchar(40),@city varchar(20),

@state char(2),@zip char(5))

As

insert store values(@stid,@stnm,@staddr,@city,@state,@zip)

   - a procedure store is created.

 we can executes the procedure as follows

Exec insert_stores '111','XYZ store','100 CTH Road','Chennai','TN','20'

'Stored procedure which returns a value'

         The stored procedure that returns a value.

create procedure retval10(@sum int output)

As

      select @sum = sum(salary) from employee

     

--select '@sum' = sum(salary) from emp2

declare @s int

exec retval10 @s output

select @s as sumofsal

  

-sp retval1 with argument @sum is created.The sp retval1 returns @sum.

It is got by the variable @s and printed.

drop procedure retval1

'Creating procedure groups'

create procedure groupA;1

As

select * from stud

exec groupA;1

create procedure groupA;2

As

select * from student

exec groupA;2

-we can create two procedure with same name groupA.

when we drop groupA both of them are dropped.

drop procedure groupA

'we can create sp for multiple tables'

      alter procedure multi

as

select * from stud

select * from course

exec multi