Stored procedure in Sql Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Stored Procedure

The stored procedure is a set of SQL Statements. In stored procedure we can accept the input and return the output. It is precompiled object. Stored procedure increases the performance of the database As they are recompiled.

Syntax of Stored Procedure:-

Create procedure Proc_Name

As

Begin

                SQL Statements

End

Example of Stored Procedure

Following is a procedure in which we select The data from table student

Create proc Proc_Select

As

Begin

Select * from student;

End

To execute the procedure We have to run the following command.

Execute Proc_name

After running this procedure you came to know how procedure create and run. Now let’s move the parameterized procedure

Parameterized Procedure

The parameter can be both input and output. If you are The programmer then remembers how you create your function in your programming Language.

Yes, same we create here.

Example of Parameterized procedure

Suppose we want to create a procedure for insert in The table. We need data to insert in the table which will be passed to the Procedure through parameters.

create proc Proc_insert1 (@id int, @name varchar (100), @course varchar(100), @fees int)

as

begin

insert into student values(@id, @name, @course, @fees);

end

To Execute this statement

exec Proc_insert 1,'neha', 'php', 6000

Procedure with output parameter

Suppose we want to show the max Salary. If we simply creates procedure to show salary (without output parameter), Then we can not use this output in future. If we want to use this output Outside the procedure, in this case we have to create procedure with out Parameter.

For example:-

create proc porc_maxsal(@sal int out)

as

begin

select @sal=MAX(salary) from Emp;

end

Execution of this procedure

declare @salary int

exec porc_maxsal @salary out;

select @salary

to execute this procedure we first have to declare the output variable which will hold the output. After execution we can use this @salary output in the next query or we can pass it to another procedure too.

Procedure with Return Value

In the above example we pass output parameter to the procedure and hold output in this variable. In the following example we didn’t pass any variable into the procedure. Infact procedure will return the output(as we use function in out programming language like c, java)

For Example:-

create proc porc_MaxSalWithRet

as

begin

declare @sal int

select @sal=MAX(salary) from Emp;

return @sal;

end

Execution of this procedure

declare @salary int

exec @salary=porc_MaxSalWithRet;

select @salary

Calling procdure from another Procedure

As we create porc_MaxSalWithRet above. Now we are crating another procedure which will execute porc_MaxSalWithRet inside it and use its output.

create proc porc_ProcInProc

as

begin

declare @salary int

exec @salary=porc_MaxSalWithRet;

select * from emp where salary=@salary;

end

exec porc_ProcInProc

hope you enjoyed this article. For any query you can mail me at malhotra.isha3388@gmail.com.

Join our Facebook page to get updates of Jobs, Interview Question, Course Details and Study Material

https://www.facebook.com/thetechaltum