All Type Coding

Search Here

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.

 

No comments :

Post a Comment