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
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:-
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.
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)
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