What Exactly is the Difference Between Input and Output Parameters


  • What exactly is the difference between input and output parameter? The code below was used in a book on ADO .NET to demonstate the use of Input and Output Parameters.:
    Dim strConn, strSQL As String
            strConn = "Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True"
            strSQL = "SELECT @UnitPrice=UnitPrice,@UnitsInStock=UnitsInStock " & _
            "FROM Products WHERE ProductName = @ProductName"
            Dim cn As New SqlConnection(strConn)
            Dim cmd As New SqlCommand(strSQL, cn)
            Dim pUnitPrice, pInStock, pProductName As SqlParameter
            pUnitPrice = cmd.Parameters.Add("@unitPrice", SqlDbType.Money)
            pUnitPrice.Direction = ParameterDirection.Output
            pInStock = cmd.Parameters.Add("@UnitsInStock", SqlDbType.NVarChar, 20)
            pInStock.Direction = ParameterDirection.Output
            pProductName = cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar, 40)
            pProductName.Value = "Chai"
    My observation is that if I run the same query without the output parameters ie @UnitPrice and @UnitsInStock it will still return the same values eg:

    SELECT UnitPrice, UnitsInStock FROM Products WHERE ProductName = 'Chai'

    If both queries return the same values what exactly is the meaning and use of output parameters?

    I will appreciate simple down-to-earth clear explanation

    Only performance counts!
    Thursday, April 23, 2009 1:57 PM


  • More often than not output parameters are used in stored procedures, where you may only want to return a single value or a value from an action query (like INSERT, UPDATE or DELETE) instead of a SELECT query.

    For example, one might use an output parameter to return an auto-generated primary key (or identity column) value after execution of an INSERT statement.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, April 23, 2009 5:49 PM