Select specific columns from the stored procedure

Question

I have a stored procedure in other database which is maintained by other team. Assume that it is currently returning 3 columns, and my system only needs those 3 columns but the other team can add few more columns for their own use which is causing my system to fail.

Other database SP

ALTER PROCEDURE FirstSP 

AS
BEGIN
    
    SET NOCOUNT ON;

    CREATE TABLE #A (Id INT, Name VARCHAR(200), Amount VARCHAR(100), TestColumn INT)
    INSERT INTO #A VALUES
    (1,'ABC','23',1), (2,'CDF','35454',2), (3,'hjhj','9809909',3)

    SELECT * FROM #A

    DROP TABLE #A
  
END
GO

And below is my query, which was only expecting 3 columns from the source

CREATE TABLE #MyTable (Id INT, Name VARCHAR(200), Amount INT)

INSERT INTO #MyTable
EXEC dbo.FirstSP;  

SELECT * FROM #MyTable

DROP TABLE #MyTable

Is there any way I can provide the column list?

This is what I am trying but it seems that I can't use server name as the parameter

DECLARE @ServerName VARCHAR(100) = @@SERVERNAME

SELECT * FROM OPENQUERY(@ServerName,'EXEC dbo.FirstSP')

My whole problem is to just select required columns from the SP. SP can have many columns in future.

Answer 1

Try this:

/*
    -----------------------------------------------------------------------
    Replace <SERVERNAME>\<INSTANCENAME>], <DATABASENAME> with your names
*/  ------------------------------------------------------------------------

-- First, enable Data Access (One time only)    
EXEC sp_serveroption '<SERVERNAME>\<INSTANCENAME>', 'DATA ACCESS', TRUE;

-- Then SELECT just the fields you need
SELECT  ID, Name, Amount
FROM    OPENQUERY([<SERVERNAME>\<INSTANCENAME>], 'EXEC <DATABASENAME>.dbo.FirstSP')