Previous SQL Articles :- https://sagarjaybhay.com/category/sql-server/
What Is Stored Procedure?
A stored procedure is a set of SQL statements. We group the required SQL statement which we want to use every time and assign a name to it. Doing this what you achieve is that you prepared a SQL code and you save it. When you required this again you just need to call name whatever you gave.
Suppose you have on query select * from employee; this query you want over and over again save it as a procedure.
You can not use stored-procedure in a select clause and where clause.
General Syntax of Procedure:
CREATE PROCEDURE procedure_name AS begin sql_statement end GO;
create procedure GetAllEmployee --Create stored procedure syntax as begin select * from Employee; end
- In this above query GetAllEmployee is the procedure name which we gave. Please avoid sp prefix for procedure name this suggested by Microsoft as in-built procedure given by Microsoft is starting with letters sp.
- As keyword is used to separate the heading and body of stored procedure.
- After that, we have begin and end statement and if you have only one SQL statement then begin and end keywords are optional. But even if you have one SQL statement it is good practice to have to begin and end.
- Create a Procedure is a syntax for creating a procedure.
How to execute a stored procedure?
There are different ways to call procedures in that we have the following way.
- Stored_procedure_name and press F5 to run on SQL server management studio
- Exec Stored_procedure_name
- Execute Stored_procedure_name
How to Delete or Drop stored procedure?
You can use a drop procedure keyword with the stored procedure name.
drop procedure GetAllEmployee; -- drop the store procedure
How to get stored procedure text in the SQL server?
Use sp_helptext in-built procedure and pass the procedure name then you will get all stored procedure syntax.
sp_helptext GetAllEmployee -- get the text of stored procedure
How to alter stored procedures in the SQL server?
You can use alter keyword along with procedure name and change the SQL statement which you want to change and run that statement your procedure will be altered.
alter procedure GetAllEmployee -- alter a stored procedure as begin select top 10 * from Employee; end
How to Create Paramatrize stored Procedure in SQL server?
To create parametrize procedure use below general syntax.
Create procedure Proc_name @param1 data_type,@para2 data_type As Begin SQL statement End;
create proc GetEmpBasedOnParameter -- create parametrize stored procedure @departmentid int, @gender varchar(10) as begin select * from Employee where DepartmentID=@departmentid and Gender=@gender; end
- In the above-code syntax, we have @departmentid is integer parameter and @geneder is the second parameter which has varchar datatype.
- The rest of the procedure syntax is the same but if you see a select statement we use @departmentid and @geneder parameter in where clause and this parameter we pass when we call this procedure.
- If you don’t pass parameters for parametrize procedure you will get errors.
- We can change the order of parameter but we need to explicitly define them in the procedure call.
exec GetEmpBasedOnParameter @gender='female',@departmentid=10 --run stored procedure syntax with parameters position not required
Stored Procedure with Output Parameter
create procedure GetGenderwisecount -- procedure with an output parameter @gender varchar(10), @empCount int out as begin select @empCount = count(empid) from Employee where Gender=@gender end;
In the above procedure, we create a parametrize procedure with an output parameter. And this procedure takes gender as input and produces a total count of that gender as output so declared @empcount int out this is our output parameter we can declare @empcount int output like also both will result same.
How to call a stored procedure which returns the output parameter?
If our stored procedure return output parameter then we need to catch that output in a variable. So when we call this procedure we need to declare a variable that is kind of scalar and gets that output parameter.
After declaring the output parameter using declared syntax and after this when you call execute statement you need to specify variable without or output else it will not get the result in our declared output parameter. The value will get is null if you not declare a variable without parameter.
The general syntax to declare output parameter is
@product_count INT OUTPUT declare @empcount int exec GetGenderwisecount 'feMale',@empcount out print(@empcount)
We can have multiple output parameters in stored procedures.
Stored Procedure: Output parameter vs return value
Create a stored procedure that will return the value.
create procedure GetGenderwisecount2 -- procedure with a return value @gender varchar(10) as begin return (select count(empid) from Employee where Gender=@gender) end;
In the above procedure, we use return statement and in braces, we write a query which counts the emp based on gender and in this we use these braces because it will execute first then it will pass that result to return statement.
To run the above procedure we use below code
declare @count int exec @count= GetGenderwisecount2 'male' print(@count)
In this, we collect the return value in the @count variable and we are not using the output parameter in our create procedure syntax. The above call procedure works perfectly fine as It returns the number value.
Always remember, when you run your stored procedure it will return the integer value which is the status of the procedure.
Now we try to return string value using the return statement.
Create procedure GetEmployeeNamebyID @id int as begin return (select Employee.full_name from Employee where EmpID=@id); end;
In above, create stored procedure syntax we pass if as input parameter and try to return the name value but when you try to execute this procedure it will throw an error
exec GetEmployeeNamebyID 1
Msg 245, Level 16, State 1, Procedure GetEmployeeNamebyID, Line 5 [Batch Start Line 91]
Conversion failed when converting the varchar value ‘Harcourt Loalday’ to data type int
So point to remember the return value of the stored procedure is always be an integer.
Difference between Output parameter and return value
|Return Value||Output parameter|
|It returns only integer data type value||It returns any kind of data type supported by the SQL server.|
|You can return only one value.||You can return any numbers of the output parameter.|
Advantages of Stored Procedure
- The best benefit of a stored procedure is that they are capable of reusing the execution plan. Execution plan means when you fire a query SQL server first checks the syntax of a query, after that it will compile that query and last it generates an execution plan. In simple words, an execution plan is for getting data from the database which is the best way to retrieve that data. So you are using a stored procedure, so SQL server cache that execution plan and stored it in memory, and when you again call that stored procedure it will not doing all process like syntax checking, compiling query and generate execution plan rather than this it will use cached execution plan so ultimately performance increase. As compared if you use to query it will generate a cache plan query also but small changes in the query result in all steps doing again.
- It resides on a server so any application wants to use that procedure they can use it. So maintainability and reusability achieve.
- It provides good security.
- It avoids SQL injection attacks.