CODEWITHSUNDEEP
c#sql-serverstored-proceduresoutput
Naveen Kumar V
Naveen Kumar V

Reputation: 2809

Second output value from insert command is not returned in stored procedure

When I use a single insert command in stored procedure to return the primary key of inserted row, the C# code worked fine.

But when trying to insert a row in 2 tables and to get the primary keys, C# code is throwing the following error "Table2PK".

MSSQL stored procedure script:

CREATE PROCEDURE [dbo].[usp_WriteBackStatus] 
    @QtyProduced decimal,
    @QuantityToIssue decimal,
    @Table1PK nvarchar OUTPUT,
    @Table2PK nvarchar OUTPUT
AS
BEGIN  
      INSERT INTO Table1 (QuantityProduced)
      OUTPUT inserted.Table1PK
      VALUES (@QtyProduced)

      INSERT INTO Table2 (QuantityToIssue) 
      OUTPUT inserted.Table2PK
      VALUES (@QuantityToIssue)

END
GO

C# Code:

using (var sqlConnection = new SqlConnection (mConnectionStringSrc)) {
     sqlConnection.Open ();
     using (var sqlCommand = sqlConnection.CreateCommand ()) {
        sqlCommand.Parameters.Clear ();
        sqlCommand.CommandType = CommandType.StoredProcedure;
        sqlCommand.CommandText = "usp_WriteBackStatus";
        sqlCommand.Parameters.Add (new SqlParameter (QtyProduced, 10));
        sqlCommand.Parameters.Add (new SqlParameter (QuantityToIssue, 5));            
        SqlParameter outParam = new SqlParameter ("@Table1PK", SqlDbType.NVarChar, 100);
        outParam.Direction = ParameterDirection.Output;
        sqlCommand.Parameters.Add (outParam);
        outParam = new SqlParameter ("@Table2PK", SqlDbType.NVarChar, 100);
        outParam.Direction = ParameterDirection.Output;
        sqlCommand.Parameters.Add (outParam);
        using (var sqlDataReader = sqlCommand.ExecuteReader ()) {
            while (sqlDataReader.Read ()) {
              var reportedID1 = sqlDataReader["Table1PK"].ToString ();
              var reportedID2 = sqlDataReader["Table2PK"].ToString (); // ERROR "Table2PK" IS THROWN HERE!!!
              Console.WriteLine ($"{reportedID1} {reportedID2}");
            }
        }
    }
}

As suggested in other SO answers, I tried using table variable to store the output and to set output variables, but I got the following error C# code.

Arithmetic overflow error converting expression to data type nvarchar. (at .ExecuteReader() line)

Script used while using table variable:

DECLARE @OutputData1 table (Table1ID nvarchar);
DECLARE @OutputData2 table (Table2ID nvarchar);
....
OUTPUT inserted.Table1PK INTO @OutputData1 (Table1ID)
OUTPUT inserted.Table2PK INTO @OutputData2 (Table2ID)
....
SELECT @Table1PK = Table1ID, @Table2PK = Table2ID FROM @OutputData1, @OutputData2;
... END ...

Upvotes: 1

Views: 99

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

The output clause in SQL Server returns a table.
This means that your stored procedure is actually returning two tables, each with a single record.
The IDataReader interface (and therefor, SqlDataReader which implementets it) has a method called NextResult() that is used to advance the DataReader from the current result set to the next one - so you should do something like this in your c# code:

string reportedID1 = null, reportedID2 = null;

if(sqlDataReader.Read ()) {
    reportedID1 = sqlDataReader["Table1PK"].ToString ();
    if(sqlDataReader.NextResult())
    {
        if(sqlDataReader.Read ()) {
            reportedID2 = sqlDataReader["Table2PK"].ToString ();
        }
    }
}

// you probably want to check that they are not both null...
Console.WriteLine ($"{reportedID1} {reportedID2}");

Upvotes: 1

Related Questions