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 QUERY_TABLE=’‘MARA’‘,FIELDS=’‘’ + @FIELDS + ‘’‘, ROWCOUNT=1’
EXEC (@RunStoredProcSQL) AT <Linked_Server_Name>;