Using Output Parameters with a Stored Procedure

One of the ways that you can use a stored procedure is to have it return data through output parameters. Then after the call to the stored procedure you can display the values returned to you. For example, you might use a stored procedure to return summary information about a table. This could be done through output parameters. The technique presented in this section of the chapter shows you how to use output parameters.


USE IT A Command object contains a collection of Parameter objects. These parameters are values that the stored procedure passes back to you after the call is complete. This ASP.NET page calls a stored procedure that returns values. The values returned are the total number of records in the Employees table and the name of the employee who is paid the most.

The stored procedure has this definition:

CREATE PROCEDURE Tablelnfo
@RecordCount integer OUTPUT,
@TopSalaryName varchar(100) OUTPUT
AS
Select @RecordCount = Count(EmpID) From Employees Select @TopSalaryName = LastName + ' + FirstName
From Employees Where Salary = (Select Max(Salary) From Employees)
Totice that the two parameters are declared as output parameters. In the first query, one of the put parameters is set to the record count. In the second query the other parameter is set to the name he employee who is paid the most.

That stored procedure is called when the ASP.NET page loads:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
[n that procedure you will need a Connection object:
Dim DBConn as SQLConnection that points to the SQL Server database:
DBConn = New SQLConnection("server=localhost;" _
& "Initial Catalog=TT;"
& "User Id=sa;"
& "Password=yourpassword;")
You will need a Command object: Dim DBSP As New SQLCommand

You also will need two Parameter objects. The first has its name match the first parameter in the stored procedure and is of the same data type:

Dim parRecordCount as New _ SqlParameter("@RecordCount", SqlDbT e.Int)

The second parameter is set to the name and type of the second output parameter in the stored procedure:

Dim parTopSalaryName as New _ SqlParameter("@TopSalaryName", SqlDbType.VarChar, 100)

Both parameters are set to output parameters using the Direction property:

parRecordCount.Direction = ParameterDirection.Output parTopSalaryName.Direction = ParameterDirection.Output

Next, those parameters are added to the Parameters collection of the Command object:

DBSP.Parameters.Add(parRecordCount) DBSP.Parameters.Add(parTopSalaryName)

The CommandText property of the Command object is set to the name of the stored procedure: DBSP.CommandText = "TableInfo" and the command type is set to stored procedure: DBSP.CommandType = CommandType.StoredProcedure

The Command object will connect to the database through the Connection object:

DBSP.Connection = DBConn DBSP.Connection.Open
The stored procedure can then be executed: DBSP.ExecuteNonQuery()

After the call to the stored procedure you can use the values in the output parameters through the Value property:

lblMessage.Text = "Total Employees: "
& parRecordCount.Value _
& "

Highest Paid: "
& parTopSalaryName.Value
Visitors would then see through the Label control the total number of employees and the highest salary amount.