Sql server, .net and c# video tutorial: Stored procedures with output parameters

In this video, we will learn about, creating stored procedures with output parameters. Please watch Part 18 of this video series, before watching this video.

To create an SP with output parameter, we use the keywords OUT or OUTPUT. @EmployeeCount is an OUTPUT parameter. Notice, it is specified with OUTPUT keyword.
Create Procedure spGetEmployeeCountByGender
@Gender nvarchar(20),
@EmployeeCount int Output
as
Begin
Select @EmployeeCount = COUNT(Id)
from tblEmployee
where Gender = @Gender
End

To execute this stored procedure with OUTPUT parameter

1. First initialise a variable of the same datatype as that of the output parameter. We have declared @EmployeeTotal integer variable.
2. Then pass the @EmployeeTotal variable to the SP. You have to specify the OUTPUT keyword. If you don't specify the OUTPUT keyword, the variable will be NULL.
3. Execute

Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Female', @EmployeeTotal output
Print @EmployeeTotal

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'
else
Print '@EmployeeTotal is not null'

You can pass parameters in any order, when you use the parameter names. Here, we are first passing the OUTPUT parameter and then the input @Gender parameter.

Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender @EmployeeCount = @EmployeeTotal OUT, @Gender = 'Male'
Print @EmployeeTotal

The following system stored procedures, are extremely useful when working procedures.
sp_help SP_Name : View the information about the stored procedure, like parameter names, their datatypes etc. sp_help can be used with any database object, like tables, views, SP's, triggers etc. Alternatively, you can also press ALT+F1, when the name of the object is highlighted.

sp_helptext SP_Name : View the Text of the stored procedure

sp_depends SP_Name : View 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 abput to drop. sp_depends can also be used with other database objects like table etc.

Note: All parameter and variable names in SQL server, need to have the @symbol.

10 comments:

AnonymousJanuary 2, 2017 at 9:45 AM
Hi
thank u for this tutorial
but I get an error (Must declare the scalar variable "@ToltalCount".) when I execute my query
here's the query:

CREATE PROCEDURE proc_GetEmployeesCount
@EmpGender nvarchar(20),
@EmpCount int OUTPUT
AS
Begin
SELECT @EmpCount = COUNT(ID) FROM tblEmployee WHERE Gender = @EmpGender
END

Declare @TotalCount int
Execute proc_GetEmployeesCount @empCount = @TotalCount OUT , @empGender = 'Male'
Print @ToltalCount
UnknownJanuary 3, 2017 at 3:21 AM
YOU CREATE STORE PROCEDURE IS CORRECT BUT IN DECLARE SECTION WRITTEN SOMETHING WRONG............
CREATE PROCEDURE proc_GetEmployeesCount
@EmpGender nvarchar(20),
@EmpCount int OUTPUT
AS
Begin
SELECT @EmpCount = COUNT(ID) FROM tblEmployee WHERE Gender = @EmpGender
END
This is correct.........now type

declare @totalcount int
excute proc_GetEmployeesCount 'male/female(what u want)',@totalcount output
print @totalcount
AnonymousMarch 6, 2017 at 6:52 PM
Anyone knows who to create a procedure where the table name is also a parameter.
I wrote a small procedure but could find a way to print the results yet.

-- Create a procedure where th etable name is a parameter
Create Procedure spGetEmployeeCountByGender2
@TableName nvarchar(20) OUTPUT
As
Begin

SET NOCOUNT ON;
DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'SELECT TOP 10 * INTO #Temp_Table_One
FROM ' + QUOTENAME(@TableName)
+ N' SELECT * FROM #TableName '

End

DECLARE @Sql NVARCHAR(MAX)
DECLARE @MyOutput NVARCHAR(MAX)
Execute spGetEmployeeCountByGender2 @TableName = tblEmployee
Select @MyOutput = @Sql

Here is the code to create the table:

Create table tblEmployee
(
ID int primary key,
Name nvarchar(50),
Gender nvarchar(50),
Salary int,
DepartmentId int foreign key references tblDepartment(Id)
)
Go

Insert into tblEmployee values (1, 'Tom', 'Male', 4000, 1)
Insert into tblEmployee values (2, 'Pam', 'Female', 3000, 3)
Insert into tblEmployee values (3, 'John', 'Male', 3500, 1)
Insert into tblEmployee values (4, 'Sam', 'Male', 4500, 2)
Insert into tblEmployee values (5, 'Todd', 'Male', 2800, 2)
Insert into tblEmployee values (6, 'Ben', 'Male', 7000, 1)
Insert into tblEmployee values (7, 'Sara', 'Female', 4800, 3)
Insert into tblEmployee values (8, 'Valarie', 'Female', 5500, 1)
Insert into tblEmployee values (9, 'James', 'Male', 6500, NULL)
Insert into tblEmployee values (10, 'Russell', 'Male', 8800, NULL)
Go
UnknownSeptember 10, 2018 at 3:17 AM
You are simple awesome man.. Bundle of Thanks
UnknownApril 13, 2020 at 6:52 AM
While creating the store procedure we are not using declare keyword to declare the variable like @gender and @employee code. But @the time executing the sp we are using declare keyword. why, please explain??

It would be great if you can help share these free resources

Stored procedures with output parameters - Part 19

10 comments: