Skip to main content

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>;