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)
            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

    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

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

            .CommandTimeout = 0

        End With


    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?


    Friday, July 31, 2009 2:37 PM


All replies