I would like to do something like this;
SELECT * INTO #tmpADD EXEC mySPROC 'Params'
SQL Server doesn’t support this and I don’t want to use OPENQUERY like this.
Select @@ServerName
EXEC sp_serveroption @@ServerName, 'DATA ACCESS', TRUE
SELECT *
INTO #tmpADD
FROM OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.mySPROC 1')
Using OPENROWSET will get the job done, but it will incur some additional overhead for opening up local connections and marshalling data. It also may not be an option in all cases since it requires an ad hoc query permission which poses a security risk and therefore may not be desired. Also, the OPENROWSET approach will preclude the use of stored procedures returning more than one result set. Wrapping multiple inline table-value user-defined functions in a single stored procedure can achieve this.
The first work around is to define structure of return values of stored procedure and create temp table.
CREATE TABLE #tmpADD
(
COL1 INT,
COL2 INT
)
INSERT INTO #tmpADD
Exec mySPROC 'Params'
This will work but if we ever add additional columns to the mySPROC stored procedure, this will blow up.
The second work around is to use an inline table-valued user-defined function. This is essentially a stored procedure (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement.
If we still have a driving need for a stored procedure, we can wrap the inline table-valued user-defined function with a stored procedure. The stored procedure just passes parameters when it calls select * from the inline table-valued user-defined function.
So for instance, Here is an inline table-valued user-defined function to get a list of customers for a particular region:
CREATE FUNCTION CustomersByRegion
(
@RegionID int
)
RETURNS TABLE
AS
RETURN
SELECT *
FROM customers
WHERE RegionID = @RegionID
GO
We can then call this function to get the results as such:
SELECT * FROM CustomersbyRegion(1)
Or to do a SELECT INTO:
SELECT * INTO CustList FROM CustomersbyRegion(1)
If we still need a stored procedure, then wrap the function as such:
CREATE PROCEDURE mySPROC
(
@regionID int
)
AS
BEGIN
SELECT * FROM CustomersbyRegion(@regionID);
END
GO
I think this is the most ‘hack-less’ method to obtain the desired results. It uses the existing features as they were intended to be used without additional complications. By nesting the inline table-valued user-defined function in the stored procedure, we have access to the functionality in two ways. Plus! We have only one point of maintenance for the actual SQL code.
If inline table value functions returns more than one schema then its is not possible to use IF statement in it. In this case we have to use table value function not inline table value function;
-- =============================================
-- Author: Shahzad Khan
-- Create date: 2/1/2022
-- Description: returns zoo data
-- SELECT * FROM [dbo].[fn_getZooAnimal](2)
-- =============================================
CREATE FUNCTION fn_getZooAnimal
(
-- Add the parameters for the function here
@Id int
)
RETURNS
@Table_Var TABLE
(
-- Add the column definitions for the TABLE variable here
Id int,
AnimalName NVARCHAR(50)
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
IF (@Id = 1)
BEGIN
INSERT @Table_Var
SELECT @Id, 'Monkey'
END
ELSE
BEGIN
INSERT @Table_Var
SELECT @Id, 'Deer'
END
RETURN
END
GO
Resources
https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table?page=1&tab=votes#tab-top
