Stored procedures with output parameters - Part 19

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:

  1. 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

    ReplyDelete
    Replies
    1. Hello bro,
      When you call Print, you write wrong name of the OUTPUT, so the SP can't execute

      Delete
    2. Hi bro,
      please check the declare variable name and print variable name.

      Delete
    3. Declare @TotalCount int
      Execute proc_GetEmployeesCount
      @empCount = @TotalCount
      OUT , @empGender = 'Male'

      Print @ToltalCount <<<<<<<<totalcount is writen as toltal

      Delete
    4. hi bro
      Declare @gender nvarchar(20),@TotalCount int
      Execute proc_GetEmployeesCount 'Male',@TotalCount out
      Print @ToltalCount

      Delete
  2. 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

    ReplyDelete
  3. 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

    ReplyDelete
  4. You are simple awesome man.. Bundle of Thanks

    ReplyDelete
  5. 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??

    ReplyDelete

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