# SQL Linked Server: Execute a Stored Procedure with input table.

**URL:** <https://community.cdata.com/t/sql-linked-server-execute-a-stored-procedure-with-input-table/400>\
**Category:** Editions\
**Tags:** article, sql-gateway, odbc\
**Created:** [September 18, 2023, 6:57pm UTC](https://community.cdata.com/t/sql-linked-server-execute-a-stored-procedure-with-input-table/400 "2023-09-18T18:57:50Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ilia\_N](https://avatars.discourse-cdn.com/v4/letter/i/9fc348/32.png) [@Ilia\_N](https://community.cdata.com/u/Ilia_N)\
**Post date:** [September 18, 2023, 6:57pm UTC](https://community.cdata.com/t/sql-linked-server-execute-a-stored-procedure-with-input-table/400/1 "2023-09-18T18:57:50Z")

</div>

There are situations in which Stored Procedures can receive both primitive values and tables as parameters. One example of this is illustrated by the RFC\_READ\_TABLE function module within the SAP ERP provider, which can be executed as a stored procedure.

_EXEC RFC\_READ\_TABLE @QUERY\_TABLE=‘MARA’, @FIELDS=‘FIELDS#TEMP’, @ROWCOUNT=1_

The QUERY\_TABLE and ROWCOUNT parameters are supplied as primitive values. In the context of this stored procedure, FIELDS is an input table. To supply this input, you can populate a temporary table using the #TEMP syntax along with INSERT statements. For example:

_INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES (‘MANDT’);_

_INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES (‘MATNR’);_

_INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES (‘ERSDA’);_

_INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES (‘ERNAM’);_

However, when dealing with an SQL Linked Server, using the #TEMP table as an input can be challenging. Instead, you can transform the #TEMP table into JSON format and then proceed to execute the stored procedure, as demonstrated in the following example:

_CREATE_ _TABLE_ _#TEMP_ _(__FIELDNAME_ _NVARCHAR(__100__))_

_INSERT_ _INTO_ _#TEMP_ _( __FIELDNAME__ )_ _VALUES (‘MANDT’);_

_INSERT_ _INTO_ _#TEMP_ _( __FIELDNAME__ )_ _VALUES (‘MATNR’);_

_INSERT_ _INTO_ _#TEMP_ _( __FIELDNAME__ )_ _VALUES (‘ERSDA’);_

_INSERT_ _INTO_ _#TEMP_ _( __FIELDNAME__ )_ _VALUES (‘ERNAM’);_

_DECLARE_ _@FIELDS_ _NVARCHAR(MAX)_

_SET_ _@FIELDS_ _= (SELECT_ _\*_ _FROM_ _#TEMP_ _FOR_ _JSON_ _AUTO)_

_SELECT_ _@FIELDS_

_DECLARE_ _@RunStoredProcSQL_ _VARCHAR( __1000__ );_

_SET_ _@RunStoredProcSQL_ _=_ _‘EXEC RFC\_READ\_TABLE&nbsp; QUERY\_TABLE=’‘MARA’‘,FIELDS=’‘’_ _+_ _@FIELDS_ _+_ _‘’‘, ROWCOUNT=1’_

_EXEC ( __@RunStoredProcSQL__ )_ _AT_ _\<Linked\_Server\_Name\>__;_