SQL How to use table variable in Openrowset

Question

I use openrowset to execute OperatorsCalc procedure.

How can I send declared parameters:

@operators (OperatorTableType)
@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

Which dbms product is this for? (Doesn't look like ANSI SQL.) — jarlh
– jarlh, Commented Aug 26, 2015 at 15:36

Lukasz Szozda · Accepted Answer · 2015-08-26 16:26:48Z

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.

edited Aug 26, 2015 at 16:26

answered Aug 26, 2015 at 15:48

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

corso Over a year ago

it worked, thank you also for those links, they are sooooo usefull

Collectives™ on Stack Overflow

SQL How to use table variable in Openrowset

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related