First stored procedure accepts parameters - then passes them to 2nd stored procedure whose results a-CodePudding

I have a stored procedure A on server 1 that takes 2 parameters from the user, and then using a linked server (ew), pulls in the results (a table) from server 2.

ALTER PROCEDURE [DW].[StoredProcA]
    @InvFromDate date OUTPUT,
    @InvToDate date  OUTPUT
AS
    WITH CTE_Labor AS 
    (
        SELECT blabla
        FROM LinkedServer.Database.schema.table
    <lots more ctes, etc.>

For performance, I'd like to instead have a stored procedure A still accept the 2 parameters, but then pass them on to stored procedure B that sits on Server 2, and return those results back to the user.

Say - I can put the stored procedure on server 2, and call it from Server 1

DECLARE @return_value int

EXEC @return_value = [LinkedServer].[DB].[Schema].[StoredProcB]
                     @InvFromDate = '2022-10-01',
                     @InvToDate = '2022-10-31'

That works.

But I'm not clear on the syntax to do the above, but have those 2 parameters be entered by the user in stored procedure 1.

Clearly this attempt is wrong:

ALTER PROCEDURE dbo.StoredProc1
    @InvFromDate DATE, 
    @InvToDate DATE 
AS 
BEGIN 
    DECLARE @return_value int;

    EXEC @return_value = [LinkedServer].[DB].[Schema].[StoredProcB] 
                         @InvFromDate = @InvFromDate, 
                         @InvToDate = @InvToDate;

    RETURN @return_value;

    END

Edit: Maybe this attempt isn't wrong.

It works when I right click and run the stored procedure, returning both the desired table and Return Value = 0. It just doesn't work when I point our front-end GUI at it. But that might not be a question for here.

CodePudding user response：

Since you are already using a linked server you could utilise this openquery approach Insert results of a stored procedure into a temporary table

Noting the following:

OPENQUERY/ linked servers are generally bad but I'm sure you're all over this
parameter string concatenation is bad
Your wrapper proc has output parameters but I don't see any reason for it... so I've removed them. See if it makes a difference.

ALTER PROCEDURE [DW].[StoredProcA]
    @InvFromDate date,
    @InvToDate date
AS

DECLARE @sql VARCHAR(4000)
SET @sql = 'EXEC [DB].[Schema].[StoredProcB] @InvFromDate = '''   FORMAT(@InvFromDate   'yyyy-MM-dd')   ''',@InvToDate = '''   FORMAT(@InvToDate,'yyy-MM-dd')   ''''

PRINT(@sql) -- for degbugging cause this never works first time

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY([LinkedServer], @SQL)

SELECT * FROM #tmpTable

CodePudding user response：

Got it.

1.) For this method, have to go into the Linked Server, and set [Enable Promotion of Distribution Transaction] = FALSE.

2.) Syntax

Alter proc [dbo].[999_Test] 

@InvFromDate date 
,@InvToDate date  

as


IF OBJECT_ID('tempdb..#tmpbus') IS NOT NULL  drop table #tmpbus;

CREATE TABLE #tmpBus
(
 Column 1 (datatype),
 Column 2 (datatype), 
 etc. )


 INSERT INTO #tmpBus
 EXEC   [LinkedServer].[DB].Schema.[StoredProcInLinkedServerO]
    @InvFromDate,
    @InvToDate;
    
 select *
 from #tmpBus

  GO