SQL How to use table variable in Openrowset

Question

I use openrowset to execute OperatorsCalc procedure.

How can I send declared parameters:

1. @operators (OperatorTableType)

2. @number (int)

into procedure that is executed by openrowset?

DECLARE @operators OperatorTableType
insert into @operators values ('Mr James')
insert into @operators values ('Mr Johnny')

DECLARE @number int = 3

SELECT *
INTO #MyTempTableForOperators
FROM
    OPENROWSET('SQLNCLI', 'Server=apollo;Trusted_Connection=yes;',
               'SET FMTONLY OFF; 
                SET NOCOUNT ON; 
                EXEC servername.dbname.dbo.OperatorsCalc @number @operators') -- I want to sent parameters @operators and @number in here

select * from #MyTempTableForOperators

Answer 1

Try to use linked server, but it may fail if there is nested insert into temp.

CREATE TABLE #temp (...)

INSERT INTO #temp(...)
EXECUTE [servername].[dbname].[dbo].[OperatorsCalc] @number @operators;

Read also How to Share Data between Stored Procedures by Erland Sommarskog. There is good chapter about OPENQUERY.