Cannot use CommandBehavior.KeyInfo when reading from stored procedure "WITH RESULT SETS"

Question

Run the following in SQL Server 2012 or later

CREATE TABLE TestTable1
(
  Dim varchar(500),
  Measure money,
  PRIMARY KEY (Dim)
)
GO

CREATE TABLE TestTable2
(
  Dim varchar(500),
  Measure money,
  PRIMARY KEY (Dim)
)
GO

CREATE PROCEDURE [dbo].[usp_InnerSp]
(
       @Sql varchar(max)
)
AS
BEGIN
  EXEC(@sql)
END
GO

CREATE PROCEDURE [dbo].[usp_ParentSp]
AS
BEGIN
  DECLARE @sql VARCHAR(MAX);
  SET @sql = 'SELECT t1.* FROM TestTable1 t1 INNER JOIN TestTable2 t2 ON t1.Dim = t2.Dim'
  EXEC [dbo].[usp_InnerSp] @sql
  WITH RESULT SETS (
   (
    [Dim] VARCHAR(500),
    [Measure] MONEY
   )
  );
END
GO

Create a console application in C#

static void Main(string[] args)
{
    using (SqlConnection connection = new SqlConnection(@"Data Source=.\SQL2014;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;"))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand("usp_ParentSp", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo))
            {
            }
        }
    }
}

When you run the code, you get the following System.Data.SqlClient.SqlException exception:

EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.

I would expect .Net to run the stored procedure, even if it cannot detect the primary keys for whatever reason.

Question: what would you recommend? It is a bug in .Net or SQL Server?

Answer 1

Why are you even using CommandBehavior.KeyInfo when already selecting the [Dim] column (t1.*)?

Either simply not use CommandBehavior.KeyInfo (best approach).

Or change

WITH RESULT SETS (([Dim] VARCHAR(500), [Measure] MONEY));

to

WITH RESULT SETS (([Dim] VARCHAR(500), [Measure] MONEY, [PrimaryKey] VARCHAR(500)));

(worst solution).

Or change t1.* to t1.Measure and

WITH RESULT SETS (([Dim] VARCHAR(500), [Measure] MONEY));

to

WITH RESULT SETS (([Measure] MONEY, [PrimaryKey] VARCHAR(500)));

(better than 2 but still worse than 1).