Stored procedures with output parameters in Sql server

Here we will learn How create an stored procedure with output parameter, we use the keywords OUT or OUTPUT. We have a table tblEmployee like above

Create Procedure spEmployeeCountByGender

@Gender varchar(20),

@EmployeeCount int Output

as

Begin

 Select @EmployeeCount = COUNT(Id)

 from tblEmployee

 where Gender = @Gender

End

 @EmployeeCount is an OUTPUT parameter. Notice, it is specified with OUTPUT keyword.Now 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 stored procedure. You have to specify the OUTPUT keyword. If you don't specify the OUTPUT keyword, the variable will be NULL.  

3-Execute the Sp like below.

Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Female',
@EmployeeTotal output
select @EmployeeTotal as [Total Employee]

 

 

 

 

 

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 like below.

Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Female',
 @EmployeeTotal
if(@EmployeeTotal is null)
Select '@EmployeeTotal is null'
else
 Select '@EmployeeTotal is not null'

 

 

 

 

 

 

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.