SELECT FROM stored procedure result

Du har en lagrad procedur, som returnerar en resultatmängd och vill spara ned resultatet i en tabell, men känner inte till i förväg hur den resultatmängd som returneras ser ut eller vill helt enkelt slippa behöva ta reda på det.

Fram till och med SQL Server version 2008 R2 gick det att göra en utsökning från en lagrad procedur, som en vanlig SELECT, antingen genom en OPENROWSET eller en OPENQUERY, så här:

CREATE PROCEDURE [dbo].[procedurNamn]
@in int
AS

CREATE TABLE #table (i int);

INSERT into #table (i)
SELECT i = @in;

SELECT i from #table;

DROP TABLE #table;
GO

-- Turn on Ad Hoc Distributed queries for the OPENROWSET selection
-- "By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When 
-- this option is set to 1, SQL Server allows ad hoc access." - https://msdn.microsoft.com/en-us/library/ms187569.aspx
-- SET FMTONLY OFF to make sure only meta data is not returned
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

IF OBJECT_ID('tempdb..#tabell') IS NOT NULL DROP TABLE #tabell;
SELECT *
INTO #tabell
FROM OPENROWSET('SQLNCLI','server=myServer;TRUSTED_CONNECTION=YES','SET FMTONLY OFF; SET NOCOUNT ON; EXEC myDB.dbo.procedurNamn @in = 1' );

-- Allow DATA ACCESS (server as linked server) for the OPENQUERY selection
-- "Enables and disables a linked server for distributed query access. Can be used only for sys.server entries 
-- added through sp_addlinkedserver." - https://msdn.microsoft.com/en-us/library/ms178532.aspx
-- SET FMTONLY OFF to make sure only metadata is not returned
EXEC sp_serveroption 'myServer', 'DATA ACCESS', TRUE;

IF OBJECT_ID('tempdb..#tabell') IS NOT NULL DROP TABLE #tabell;
SELECT *
INTO #tabell
FROM OPENQUERY([myServer], 'SET FMTONLY OFF; SET NOCOUNT ON; EXEC myDB.dbo.procedurNamn @in = 1');

Detta kan ju vara användbart om man till exempel skall jämföra output från en procedur, med olika inparametrar. Eller om man har en gammal och en ny version av en procedur och vill jämföra dessa. Eller… ja ni förstår. Detta går ganska snabbt om man inte behöver tänka på hur resultatet från proceduren är definierat.

Du kanske lade märke till att jag skapade en temptabell i proceduren? Det är den som gör att det vi just gjorde ovan inte går att göra i SQL Server 2012 och framåt.

Det beror på det här:

DECLARE @sql nvarchar(500)
SET @sql = 'EXEC myDB.dbo.procedurNamn @in = 1'

EXEC sp_describe_first_result_set @sql

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 13
The metadata could not be determined because statement 'INSERT into #table (i)
--insert into @table
SELECT i = @in' in procedure 'procedurNamn' uses a temp table.

“The metadata could not be determined…”

Från och med SQL Server 2012 är SET FMTONLY “deprecated” och metadata kan inte stängas av eller sättas på, på det sättet. Istället använder sig 2012 av sp_describe_first_result_set. Och sp_describe_first_result_set hittar inte metadata om temptabellen, eftersom den skapas först när proceduren som skapar den körs.

Finns det då en lösning för det här? Jo förvisso,  men det är en lösning som nog inte riktigt uppfyller de förväntningar man har om man är van vid att kunna ställa frågan direkt, utan att behöva definiera resultatet i förväg.

Lösningen ser då ut som följande med EXECUTE WITH RESULT SETS:

IF OBJECT_ID('tempdb..#tabell') IS NOT NULL DROP TABLE #tabell;
SELECT *
INTO #tabell
FROM OPENROWSET('SQLNCLI','server=myServer;TRUSTED_CONNECTION=YES','SET NOCOUNT ON; EXEC myDB.dbo.procedurNamn @in = 1 WITH RESULT SETS((i int))' );

-- Eller

IF OBJECT_ID('tempdb..#tabell') IS NOT NULL DROP TABLE #tabell;
SELECT *
INTO #tabell
FROM OPENQUERY([myServer], 'SET NOCOUNT ON; EXEC myDB.dbo.procedurNamn @in = 1 WITH RESULT SETS ((i int))');

I vilket fall man lika gärna kan skapa sin temptabell (som man ska lagra resultatet i) i förväg och således göra det gamla vanliga:

CREATE TABLE #tabell (i int);

INSERT INTO #tabell
EXEC myDB.dbo.procedurNamn;

Eller ersätta alla sina temptabeller i proceduren med tabellvariabler. Men det var väl knappast tanken från början. Eller har du hittat en bättre lösning på detta?