.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Code Snippets >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Stored Procedure Output Parameters to Retrieve a Single Row

Posted By:Shashi Ray       Posted Date: November 30, 2009    Points: 10    Category: ADO.NET    URL: http://www.dotnetspark.com  


Stored Procedure Output Parameters to Retrieve a Single Row

You can call a stored procedure that returns the retrieved data items within a single row by means of named output parameters. The following code fragment uses a stored procedure to retrieve the product name and unit price for a specific product contained in the Products table in the Northwind database.

void GetProductDetails( int ProductID, 
                        out string ProductName, out decimal UnitPrice )
  using( SqlConnection conn = new SqlConnection(
        "server=(local);Integrated Security=SSPI;database=Northwind") )
    // Set up the command object used to execute the stored proc
    SqlCommand cmd = new SqlCommand( "DATGetProductDetailsSPOutput", conn )
    cmd.CommandType = CommandType.StoredProcedure;
    // Establish stored proc parameters.
    //  @ProductID int INPUT
    //  @ProductName nvarchar(40) OUTPUT
    //  @UnitPrice money OUTPUT
    // Must explicitly set the direction of output parameters
    SqlParameter paramProdID = 
             cmd.Parameters.Add( "@ProductID", ProductID );
    paramProdID.Direction = ParameterDirection.Input;
    SqlParameter paramProdName = 
             cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );
    paramProdName.Direction = ParameterDirection.Output;
    SqlParameter paramUnitPrice = 
             cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money );
    paramUnitPrice.Direction = ParameterDirection.Output;
    // Use ExecuteNonQuery to run the command. 
    // Although no rows are returned any mapped output parameters 
    // (and potentially return values) are populated 
    cmd.ExecuteNonQuery( );
    // Return output parameters from stored proc
    ProductName = paramProdName.Value.ToString();
    UnitPrice = (decimal)paramUnitPrice.Value; 

To retrieve a single row with stored procedure output parameters

  1. Create a SqlCommand object and associate it with a SqlConnection object.
  2. Set up the stored procedure parameters by calling the Add method of the SqlCommand's Parameters collection. By default, parameters are assumed to be input parameters, so you must explicitly set the direction of any output parameters.

Note   It is good practice to explicitly set the direction of all parameters, including input parameters.

  1. Open the connection.
  2. Call the ExecuteNonQuery method of the SqlCommand object. This populates the output parameters (and potentially a return value).
  3. Retrieve the output parameters from the appropriate SqlParameter objects by using the Value property.
  4. Close the connection.

The preceding code fragment invokes the following stored procedure.

@ProductID int,
@ProductName nvarchar(40) OUTPUT,
@UnitPrice money OUTPUT
SELECT @ProductName = ProductName, 
       @UnitPrice = UnitPrice 
FROM Products 
WHERE ProductID = @ProductID


Shashi Ray


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend