Stored procedure + table as Output parameter

Question

Hi,
We are using sql server 2016. I have stored procedure which executes another stored procedure which has OUTPUT parameter which is of type table. I need to retrieve this table and send it's data in an email. How can I do it.

Before, the OUTPUT parameter was of varchar type. Now, they converted it to table type. How can I get table data. This table has exception data. If there are no exceptions, table won't be returned. I think it returns null.

The stored procedure (uspInsertData) is given to us by vendor. We are executing it using linked sever to vendor server. We do not know the schema of table that is output.

How to get table OUTPUT parameter.

CREATE PROCEDURE [dbo].[uspGetData]
@pFileDirectory varchar(200),
@pFileName varchar(100),
@pFileFormat varchar(100) = 'ss.xml'
AS
BEGIN

SET NOCOUNT ON;  
DECLARE @sqlCmd NVARCHAR(max)   
		, @returnMessage varchar(1000)  

DECLARE @ImportedData AS TABLE  
(  
	clm1 INT NULL,   
	ID int NULL,  
	sip varchar(20) NULL  
)  

SET @sqlCmd =   
	'SELECT *  
	FROM OPENROWSET(BULK ''' + @pFileDirectory + @pFileName + ''',  
		FIRSTROW = 2,  
		FORMATFILE = ''' + @pFileFormat + ''') AS T '  

INSERT INTO @ImportedData   
	EXEC sp_executesql @sqlCmd;  
	  
*EXEC uspInsertData @ReturnMessage = @returnMessage OUTPUT*  -- I just declared @returnMessage as varchar. Actually it should be table.  

END

Does this work?
EXEC uspInsertData @ReturnMessage = select into #tblReturnMessage OUTPUT

Thanks,

Answer 1

You must know the schema of the table returned to insert into from an EXEC.

Answer 2

Table parameters in SQL Server are input only, so it is not really clear what they are returning. Maybe they are returning a result set?

If you don't know the schema of the result set that the vendor return, you will need to ask them for documentation.

Answer 3

Hi @Spunny ,

Welcome to Microsoft Q&A!

As mentioned by other experts, it would be better for you to have the details of stored procedure (uspInsertData) or the DDL of output table by vendor.

Then you could have a try with below steps:

One: create one user-defined table type.

CREATE TYPE MyType  
   AS TABLE  
      ( clm1 INT NULL,   
     ID int NULL,  
     sip varchar(20) NULL);  

Two: modify the procedure of uspInsertData like below example.

CREATE PROCEDURE dbo. uspInsertData   
   @TVP MyType READONLY  
      AS  
      SET NOCOUNT ON  
      INSERT INTO Mytable  --provide DDL of table once vendor provides  
         (  
            clm1  
            , ID  
            , sip  
            , createdate  
         )  
      SELECT *, GETDATE()  
      FROM @TVP;  
GO  

Three: modify the [uspGetData] like below:

CREATE PROCEDURE [dbo].[uspGetData]  
@pFileDirectory varchar(200),  
@pFileName varchar(100),  
@pFileFormat varchar(100) = 'ss.xml'  
AS  
BEGIN  
  
 SET NOCOUNT ON;  
 DECLARE @sqlCmd NVARCHAR(max)   
         , @returnMessage varchar(1000)  
  
 DECLARE @ImportedData  AS MyType;  
  
 SET @sqlCmd =   
     'SELECT *  
     FROM OPENROWSET(BULK ''' + @pFileDirectory + @pFileName + ''',  
         FIRSTROW = 2,  
         FORMATFILE = ''' + @pFileFormat + ''') AS T '  
 INSERT INTO @ImportedData   
     EXEC sp_executesql @sqlCmd;  
     
 EXEC dbo. uspInsertData  @ImportedData;   
  
END  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.