Return type of stored procedures that use temporary tables

[kerams]

Description

Stored procedures such as the one below have a return type Unit when invoked.

Repro steps

1. Create the stored procedure

CREATE PROCEDURE [Test]
AS
BEGIN
  CREATE TABLE #_T (ID int NOT NULL, PRIMARY KEY (ID))
  INSERT INTO #_T(ID)
  VALUES (1)

  SELECT 1
  SELECT 2
  SELECT 3
END

2. Access the stored procedure using the type provider and inspect the result type

let value = Sql.GetDataContext().Procedures.Test.Invoke ()
// val value : Unit

Expected behavior

Ideally, I'd like to see each result set fully typed. In the worst case, I'd at least want to access the result sets in a dynamic fashion.

Actual behavior

The return type of the sproc is Unit and there is no way to access the returned data.

Known workarounds

None that I know of other than getting rid of temporary tables :)

Related information

• Used database
  MSSQL
• SQLProvider version
  1.0.37

[pezipink]

I believe this is because temporary tables break the SET FMTONLY ON from returning any schema. I am not sure if the sql 2012+ metadata functions work in this scenario, if they do then we could enable them (there is a function I wrote to detect 2012+, just never got around to using the newer metadata functions)

Other than that, the problem here is at compile time not having any way to know if the sproc is just causing side effects or it is returning something, for this case. I guess we could let the user tell it somehow that it is going to return something... ?

[kerams]

Had a look around and it seems the new functions don't like temp tables either :/