Aidan Ryan

Software Developer

SqlMetal Bug With Multi-rowset Stored Procedures

| Comments

The Issue:

When a stored procedure has multiple result sets with the same column signature, only the first rowset with the given signature is included in the generated DBML.

Consider the following three stored procedures:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE PROCEDURE [GetOneWithLetters]
AS BEGIN
  SELECT 1 as [One], [Letter] FROM [dbo].[Letters]
END
GO

CREATE PROCEDURE [GetTwoWithLetters]
AS BEGIN
  SELECT 2 as [Two], [Letter] FROM [dbo].[Letters]
END
GO

CREATE PROCEDURE [BrokenInSqlMetal]
AS BEGIN
  exec [GetOneWithLetters]
  exec [GetTwoWithLetters]
END
GO

The first two stored procedures return simple result sets. The third stored procedure returns two result sets by calling first two procedures.

The DBML generated by SqlMetal is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?xml version="1.0" encoding="utf-8"?>
<Database Name="_Scratch" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
  <Table Name="dbo.Letters" Member="Letters">
    <Type Name="Letters">
      <Column Name="Letter" Type="System.String" DbType="Char(1)" CanBeNull="true" />
    </Type>
  </Table>
  <Function Name="dbo.BrokenInSqlMetal" Method="BrokenInSqlMetal" HasMultipleResults="true">
    <ElementType Name="BrokenInSqlMetalResult">
      <Column Name="One" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="Letter" Type="System.String" DbType="Char(1)" CanBeNull="true" />
    </ElementType>
  </Function>
  <Function Name="dbo.GetOneWithLetters" Method="GetOneWithLetters">
    <ElementType Name="GetOneWithLettersResult">
      <Column Name="One" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="Letter" Type="System.String" DbType="Char(1)" CanBeNull="true" />
    </ElementType>
  </Function>
  <Function Name="dbo.GetTwoWithLetters" Method="GetTwoWithLetters">
    <ElementType Name="GetTwoWithLettersResult">
      <Column Name="Two" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="Letter" Type="System.String" DbType="Char(1)" CanBeNull="true" />
    </ElementType>
  </Function>
</Database>

Note that although the [BrokenInSqlMetal] procedure returns two result sets, only one result is generated. When the stored procedure is executed via a LINQ to SQL DataContext, and reults are retrieved by calling DataContext.GetResult two times, the contents of the second result set are incorrect. The value of the One property (which should correspond to the [Two] column of the second stored proc) is not populated.

Resolution:

One fix is to swap the position of the columns in one of the rowsets. It also appears that adding a column alias to one of the child procedures will resolve the problem. Of course, this will likely require modifying the calling code at some layer of your application.

Connect:

I have posted this issue to Microsoft Connect. If it affects you, please visit and vote here.

Comments