Understand Stored Procedure in SQL Server

Introduction


Stored Procedure is a procedure which is pre-compiled SQL statement which accept input and return one or multiple value as an output.

This is a part of Procedural language which otherwise called TSQL.

If we are returning multiple value of different type then we should prefer "OUT" parameter.

If we are returning one value of type integer then we should prefer return type.


Lets Started

Lets take an example of simple stored procedure which return a result as an output.

create table test(id int primary key identity(1,1),name varchar(20),place varchar(20))

insert into test values('Raj','orissa')
insert into test values('Abi','AP')
insert into test values('Aku','MP')
insert into test values('Suresh','UP')
insert into test values('Baanu','Bihar')

CREATE PROCEDURE MYTestProc
(
    @id int
)
as
begin
   select * from test where id=@id

end

See the above stored-procedure  . We created by using the keyword "Create Procedure".You can run the procedure by pressing "F5". If want to modify after creation then we can use "Alter Procedure" keyword to modify.

I passed a parameter "@id" which can be used inside the procedure as shown in the Above code.We may pass or may not pass parameter to the stored procedure. 

"Stored procedure" Starts with "As Begin" and end with "End".
We can write our query inside of it.
It can be anything like Select,Delete,Update,Insert..etc.....

How can we execute the above procedure??

we can use "Exec" keyword to get the output.

EXEC mytestproc 1

Fig-1

See the little bit deeper and see how Output parameter works as below.

Lets Understand How to use out parameter

alter PROCEDURE MYTestProc
(
@id int,
@name varchar(20) output
)
as
begin
set @name=(select name from test where id=@id)
end

Executing the stored procedure using Out Parameter

declare @name varchar(20)

EXEC mytestproc 1,@name output

print @name

Output

Fig-2

Lets Understand How to use Return parameter

Create PROCEDURE MYTestProc

(

@name varchar(20)

)

as

begin

declare @id int

set @id=(select id from test where name=@name)

return @id                        //it will not execute the remaining statement if any
select * from test             //This statement will not execute

end

Executing the stored procedure using Return keyword

declare @id int

exec @id=MYTestProc 'Raj'

print @id

 Few things to remember about stored procedure

• We can use Create and Alter keyword while creating and modifying stored procedure respectively.

• To delete a procedure we can use DROP Procedure <Procedure_name>.

• Stored Procedure is a pre-compiled , so this is faster as compared to normal query.

• We can use Function inside a procedure.

• In return the remaining statement will not execute which comes after return.

• It is used as it used as a modular programming.

• It can be reusable so maintenance will be faster.

• It provide security.

Thanks...........

Share: