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.
Execute spGetEmployeeCountByGender 'Female',
@EmployeeTotal
if(@EmployeeTotal is null)
Select '@EmployeeTotal is null'
else
Select '@EmployeeTotal is not null'
No comments :
Post a Comment