Lets say you have a stored procedure inside SQL Server that creates and utilizes a temp table, then returns a resultset FROM the temp table.
Can you utilize this in Tableau? Sometimes. If you’re smart.
Given the following stored procedure:
CREATE Stored Procedure [dbo].[Foobar] (@foo varchar(255))
AS
CREATE TABLE #T1 (dummyVarChar varchar(255) NULL, dummyInt int not null);
INSERT #T1 VALUES (@foo, 5);
SELECT * from #T1
You’ll normally get an error like this when you try and leverage it in Tableau:
Google Click Bait: The stored procedure does not return a resultset
The reason for this behavior is (I think) Tableau attempts to return the first resultset it gets back from the stored procedure. In the case above, it would be “Inserted 1 Row” – not a true resulset.
So, be sneaky. Tell your stored procedure NOT to return those sorts of messages with SET NOCOUNT ON:
CREATE Stored Procedure [dbo].[Foobar] (@foo varchar(255))
AS
SET NOCOUNT ON
CREATE TABLE #T1 (dummyVarChar varchar(255) NULL, dummyInt int not null);
INSERT #T1 VALUES (@foo, 5);
SELECT * from #T1
Once you’ve done this, everyone is happy:
