CODEWITHSUNDEEP
c#sqlstored-procedures
user1003054
user1003054

Reputation: 31

Merge statement is not working with OUTPUT clause

I am reading a data from CSV file and putting a data in data table. this dt i am sending to stored procedure to insert/update . The below is my stored procedure :

*======================================

ALTER  PROC [dbo].[ups_InsertTVPOrderHeaders]
(
   @tvp [dbo].[TYPOrderHeaders] READONLY
)
AS
    SET NOCOUNT ON;
    DECLARE @InsertOutput TABLE
    (
      MergeAction VARCHAR(20),
      OrderHeaderID int

    );
  BEGIN TRY

  MERGE INTO dbo.OrderHeaders AS T
    USING @tvp AS S
    ON T.OrderHeaderID = S.OrderHeaderID
    WHEN MATCHED THEN 
                  UPDATE SET T.CustomerID = S.CustomerID,
                                 T.FirstName = S.FirstName,
                                 T.LastName = S.LastName

    WHEN NOT MATCHED THEN 
                     INSERT (OrderHeaderID,CustomerID,FirstName,LastName) 
                     VALUES (S.OrderHeaderID,S.CustomerID,S.FirstName,S.LastName)
        OUTPUT $action, inserted.OrderHeaderID INTO @InsertOutput;
        --OUTPUT $action, inserted.OrderHeaderID, Deleted.OrderHeaderID;
        --OUTPUT $action, DELETED.*, INSERTED.*;

      COMMIT TRANSACTION

SELECT * FROM @InsertOutput;        

  END TRY

  BEGIN CATCH

  END CATCH

*===================================================================

C# Code calling SP part

  using (SqlConnection con = new SqlConnection(_conStr))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand(procedureName, con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter param = cmd.Parameters.AddWithValue("@tvp", SqlDbType.Structured);
                    param.Value = dt;
                    int result = cmd.ExecuteNonQuery();
                    return result;
                }
            }

================================================================================== i am getting result as 0 . If i take out the ouput clause from sp. its works fine. Please can any one tell me what's wrong in output into statement. I wanted the insert/updated list of orderheaderID. Hence, i tried using output clause.

Upvotes: 0

Views: 2201

Answers (1)

GarethD
GarethD

Reputation: 69759

You are using cmd.ExecuteNonQuery(); which will not return results, only the number of rows affected. According to MSDN this is what ExecuteNonQuery does:

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

You probably want to use ExecuteReader:

using (SqlConnection con = new SqlConnection(_conStr))
{
    con.Open();
    using(SqlCommand cmd = new SqlCommand(procedureName, con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter param = cmd.Parameters.AddWithValue("@tvp", SqlDbType.Structured);
        param.Value = dt;
        using (var reader = cmd.ExecuteReader())
        {
            int result = 0;

            while (reader.Read())
            {
                // Do something with the reader, then increment result by 1 to get total rows affected
                result++;
            }
        }
        return result;
    }
}

Upvotes: 3

Related Questions