SQL Server - What is the difference between return value and output value in stored procedure

Asked By chitanya chitanya on 28-Feb-07 08:34 AM
end of post

difference between return value and output value in stored procedure

K Pravin Kumar Reddy replied to chitanya chitanya on 28-Feb-07 08:47 AM

Using Output Parameters 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)


You are not limited to a single output parameter; additionally, you can have stored procedures with both input and output parameters.

 

 

Using a Return Value
The final technique I want to talk about for returning scalar values from a stored procedure is using return values. Whenever a stored procedure finishes executing, it always returns a return value. This return value is, by default, 0. You can use the RETURN statement yourself, however, to return a scalar integer value. For example, let's revisit the store_AddNewInventoryItem, but modify it to return the ID of the newly inserted row as a return value.


CREATE PROCEDURE store_AddNewInventoryItem
(
	@ProductName	nvarchar(50),
	@Price			money
) AS

-- INSERT the new record
INSERT INTO store_Inventory(ProductName, Price)
VALUES(@ProductName, @Price)

-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()

Note that the SCOPE_IDENTITY() value is being return via a RETURN statement now, whereas in the earlier example we used a SELECT.

To retrieve the return value from a stored procedure use the same technique as with output parameters, the only difference being that you should use a Direction value of ReturnValue, as the following code snippet 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 retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int)

'IMPORTANT - must set Direction as ReturnValue
retValParam.Direction = ParameterDirection.ReturnValue

'Finally, add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(retValParam)

'Call the sproc...
Dim reader as SqlDataReader = myCommand.ExecuteReader()

'Now you can grab the output parameter's value...
Dim retValParam as Integer = Convert.ToInt32(retValParam.Value)
That's all there is to it! As I mentioned earlier, you can only return integer values through the stored procedure's return type. 
 
reference
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

A return value is - Venkat K replied to chitanya chitanya on 28-Feb-07 08:51 AM

 a single data element that is the result of a query. Essentially, you can run a query that outputs a single value. There are limits to using the “RETURN” function; they can’t do everything.

you can use output parameters to return other types of values from a procedure. These parameters can be integers, character strings, dates, and even cursors.