Stored Procedure in SQL Server

What is 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 pre compiled object. Stored procedure increases the performance of the database as they are pre compiled.

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

Parametrized Stored 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 Parametrized 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 cannot use this output in future. If we want to use this output outside the procedure, in this case we have to create procedure without 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. Infect 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 procedure from another Procedure

As we create porc_MaxSalWithRet above. Now we are creating 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