This article explains stored procedures as used in SQL server.
Introduction
A stored procedure is a procedure which is used inside SQL mainly to reduce the query. The stored procedure is a predefined statement or a pre-compiled statement. It's a pack of queries that is predefined by the user or developer. Basically each and every the stored procedure written in a specified name.
Advantages
* It will reduce the compilation time.
* Increase the performance of query
* Reduce the complexity of query.
Disadvantage
* It will occupies the memory space in database.
* Difficult to maintain.
Type of Stored Procedure
1.without argument or parameter
2.with argument or parameter
1. Without argument or parameter
In this type of stored procedure, we are not going to give any arguments or parameters while executing. Its a argument free procedure, so it's applicable to create, alter and drop a table in a database.
For example:
Table named micro with mid, mname, mcourse as records. Now we are creating a procedure for select the mid and mname from the table micro.
Create
create proc sp_selmicro
As
select mid,mname from micro
Alter
create proc sp_altmicro
As
select mid,mname from micro
Drop
drop proc sp_selmicro
2. With argument or parameter
Here we have to enter the require parameter. while we passing the parameter we have to use '@' symbol. Its must, with '@' symbol the parameter is not acceptable.
for example:
Insert
create proc sp_insmicro(@mid int,@mname varchar(20),@mcourse varchar(10))
as
insert into micro(mid,mname,mcourse) values (@mid,@mname,@mcourse)
Exe: sp_insmicro 1,'praveen','computer science'
Delete
create pro sp_delmicro(@mid)
as
delete from micro where mid=@mid
Exe: sp_delmicro 2
Summary
The stored procedure is very simple and useful statement which is fully created by user. But its difficult to maintain and also we have to remember the procedure (proc) names.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|