What Exactly is the Difference Between Input and Output Parameters

Question

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)
        cn.Open()
        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"
        cmd.ExecuteNonQuery()

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!

Answer 1

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)