A stored procedure is a part of SQL commands. If you have a situation, where you write the same query over and over again, you can save that specific query as a stored procedure and call it just by its name. Stored procedure also reduces the network traffic on website or web. In addition, create for once and run several times.
So, Let’s create a table tblEmployee, as a result, table will look like this as;
Stored Procedure without any Parameters
This simple stored procedure fetches all of the records of column Name and Gender from the table. For this, We create a simple procedure. You also can create CREATE PROCEDURE or Create Proc statement.
Create Procedure spGetEmployees
Select Name, Gender from tblEmployee
Finally, the output will look like as;
Note: Once naming convention defines user-defined stored procedures, Microsoft recommends not to use “sp_” as a prefix. All inbuilt system stored procedures constantly prefixed with “sp_”. So, this avoids any error in between user-defined and inbuilt system stored procedures.
To execute the stored procedure, you just write only your stored procedure name and then press F5 key. Opposite, you can type EXEC or EXECUTE key elements with the following procedures as;
2. EXEC spGetEmployees
3. Execute spGetEmployees
Stored Procedure with Input Parameters
This SP accepts only Gender and DepartmentID parameters. There parameters and variables contain an @ prefix in their name.
To get the result for this SP, we pass the value of variables name as @Gender and @DepartmentId parameters.
EXECUTE spGetEmployeesByGenderAndDepartment ‘Male‘, 1
Opposite, if you change the passing order, you will get an error like as ‘Error converting data type varchar to int’. In conclusion, the value of ‘Male’ into @DepartmentID parameters just passed, whereas @DepartmentID is an integer, then we face conversion error.
spGetEmployeesByGenderAndDepartment 1, ‘Male’
And, you can also write your final query to get a wise result. Once you mention the names of the procedure that does not matter the order.
EXECUTE spGetEmployeesByGenderAndDepartment @DepartmentId=1, @Gender =‘Male’
To view the text, of the stored procedure, you can use system stored procedure sp_helptext ‘SPName’
To change the stored procedure, use ALTER PROCEDURE statement:
When it encrypts the text, then use ‘WITH ENCRYPTION’ option. Once, encrypted, you are not able to view the text of the procedure. Once you modify the name it reflects your original name.
To delete the SP, use DROP PROC ‘SPName’ or DROP PROCEDURE ‘SPName’
Stored Procedure with Output Parameters
To create an SP with output parameters, either use keywords Out or Output @EmployeeCount is an Output parameter.
Now, we have a table tblEmployee
Let’s create output parameter,
Create Procedure spGetEmployeeCountByGender
@EmployeeCount int Output
Select @EmployeeCount = COUNT(Id)
where Gender = @Gender
To execute this stored procedure with OUTPUT parameter
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender ‘Female’, @EmployeeTotal output
If you don’t specify the OUTPUT keyword, when executing the stored procedure, the @EmployeeTotal variable will be NULL. Here, we have not specified OUTPUT keyword. When you execute, you will see ‘@EmployeeTotal is null’ printed.
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender ‘Female’, @EmployeeTotal
if(@EmployeeTotal is null)
Print ‘@EmployeeTotal is null’
Print ‘@EmployeeTotal is not null’
Therefore, first pass output parameters in any order, then use the parameter names. Hence, we first pass the OUTPUT parameter and then the input @Gender parameter.
Stored procedures are very useful when working procedures as following
sp_help SP_Name: To get the information about the stored procedure, like parameter variable names, their data types etc. sp_help uses any database object, like tables, views, SP’s, triggers etc.
sp_helptext SP_Name: To get the Text of the stored procedure
sp_depends SP_Name: To get the dependencies of the stored procedure. This system SP is very useful, especially if you want to check if there are any stored procedures that are referencing a table that you are about to drop.
Note: All parameter and variable names in SQL server, need to have the @symbol.
Stored Procedure Output Parameter or Return Value
In this schema, we will cover these points,
1. Definition of stored procedure return values.
2. How to differentiate in between stored procedure return values and output parameters.
3. Why choose output parameters over return values
What are stored procedure status variables
Whenever you run a stored procedure, it gives an integer status variable in return. Generally, there zero indicates success, 1 shows failure. Execute any stored procedure from the object explorer.
1. First, Right Click and select ‘Execute Stored Procedure
2. If the parameters require, then provide the values and click OK.
3. As a result that you expect, the stored procedure also returns a Return Value = 0
Therefore, Once a stored procedure executes, it gives an integer status variable in return. To clarify the difference in between output parameters and return values. We will use the table tblEmployee.
The following procedure returns a total number of employees in the Employees table, using output parameter – @TotalCount.
So, by using output parameters with return values. Now, let’s look at an example, where return status variables cannot be used, but Output parameters can be used.
In this schema, we are fetching the name of the particular employee, which is based on their unique Id, by using the output parameter @Name.
It gives an error like conversion failure from nvarchar to int value. The final status variable is an integer, and when we select an employee name and try to return that we get a conversion error.
Declare @EmployeeName nvarchar(20)
Execute @EmployeeName = spGetNameById2 1
Print ‘Name of the Employee = ‘ + @EmployeeName
So, by using return values, we return only integers. It is not possible to return more than one value by using return values. Opposite, output parameters can return any datatype and an SP can have more than one output parameters. This is the reason I prefer work by using output parameters, over RETURN values.
In addition, RETURN values indicate success or failure of the stored procedure on behalf of their result, and when we deal with nested stored procedures. Return a value of 0, that indicates success, and any non-zero value indicates failure.
Difference between return values and output parameters
Stored Procedure mainly uses for reducing the networking traffic. It saves the query time instead of writing a query again and again. Some database administrators, particularly on significant Oracle installations, do not access directly to tables because of security and data integrity, some crucial databases can only be accessed through Views and Stored Procedures. At the other side, none of the stored procedures are used at all and every SQL statement is embedded inside a dBASE .wfm file.