none
Call a stored procedure with OUTPUT parameter RRS feed

  • Question

  • Hello,

    Let say that in my SQL 2005 database I have defined this stored procedure:

        CREATE PROCEDURE [dbo].[CheckOrders] (
        @PK_Customer uniqueidentifier,
        @Amount bigint OUTPUT)
        AS
        BEGIN
            SELECT @Amount = COUNT(*) FROM Orders
            WHERE FK_Customer = @PK_Customer AND DateChecked IS NULL

            UPDATE Orders SET DateChecked = GETDATE()
            WHERE FK_Customer = @PK_Customer AND DateChecked IS NULL

            RETURN 1
        END


    and that I call this stored procedure in my vb.NET application (VS 2008):

        Dim nAmount AS Long = -1
        Dim gPK_Customer as Guid = ...

        Dim cn As New SqlClient.SqlConnection
        cn.ConnectionString = ...

        Dim com As New SqlClient.SqlCommand
        With com

            .Connection = cn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "CheckOrders"

            Dim inParam As New Data.SqlClient.SqlParameter("@PK_Customer", gPK_Customer)
            inParam.Direction = Data.ParameterDirection.Input
            .Parameters.Add(inParam)

            Dim outParam As New Data.SqlClient.SqlParameter("@Amount", nAmount)
            outParam.Direction = Data.ParameterDirection.Output
            .Parameters.Add(outParam)

            .Connection.Open()
            .CommandTimeout = 0
            .ExecuteNonQuery()
            .Connection.Close()

        End With

        com.Dispose()


    When I check the value of nAmount it stil contains the original value -1 the output value didn't return from the stored procedure although the number of records updated is 0 or more.

    The procedure was checked, it works and there are records updated and the value of @Amount is SET to a value 0 or more. I just don't get the values back. eventough the parameter @Amount is defined as OUTPUT.
    I also tried with outParam.Direction = Data.ParameterDirection.InputOutput instead of outParam.Direction = Data.ParameterDirection.Output . Same result.

    How do I get the value back from the stored procedure?

    Thanks

    Friday, July 31, 2009 2:37 PM

Answers

All replies