Using Output Parameters in Stored Procedures?

One way to retrieve scalar data in addition to a standard resultset from a stored procedure is to use one or more output parameters. An output parameter is a parameter that is passed into the SQL stored procedure, but whose value can be set in the stored procedure. This assigned parameter, then, is readable back from the application that called the stored procedure.
To use an output parameter you need to indicate that the parameter is intended for output via the OUTPUT keyword. The following snippet shows a stored procedure that returns the set of inventory items through a SELECT statement and uses an output parameter to return the average price:

CREATE PROCEDURE store_GetInventoryWithAveragePrice
(
@AveragePrice money OUTPUT
)
AS
SET @AveragePrice = (SELECT AVG(Price) FROM store_Inventory)
SELECT InventoryID, ProductName, Price, UnitsOnStock
FROM store_Inventory

To access the value of an output parameter from your ASP.NET application you need to create a parameter object whose Direction property is set to Output. After you call the stored procedure the output parameter's value is accessible through the Value property, as the

following code illustrates:
Dim myConnection as New SqlConnection(connection string)
myConnection.Open()
Dim myCommand as New SqlCommand("store_GetInventoryWithAveragePrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
'Create a SqlParameter object to hold the output parameter value
Dim avgPriceParam as New SqlParameter("@AveragePrice", SqlDbType.Money)
'IMPORTANT - must set Direction as Output
avgPriceParam.Direction = ParameterDirection.Output
'Finally, add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(avgPriceParam)

'Call the sproc...
Dim reader as SqlDataReader = myCommand.ExecuteReader()
'Now you can grab the output parameter's value...
Dim avgPrice as Decimal = Convert.ToDecimal(avgPriceParam.Value)

(The same issue regarding NULLs applies here as in the previous example...)
You are not limited to a single output parameter; additionally, you can have stored procedures with both input and output parameters.


Refered Site : http://aspnet.4guysfromrolla.com/articles/062905-1.aspx