0

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.

bbb0777
  • 165
  • 14
  • this should help https://stackoverflow.com/questions/1589466/execute-stored-procedure-with-an-output-parameter – Scott Mildenberger Oct 21 '22 at 19:22
  • 1
    *Clearly this attempt is wrong:* Looks correct to me. Have you tried it? – John Wu Oct 21 '22 at 19:41
  • Good point - it works if say, I right click & run the stored proc. It just doesn't work when I point our front-end GUI at it. But that might not be a question for here then... – bbb0777 Oct 22 '22 at 00:21
  • 1
    What does _doesn't work_ mean? Note your first proc has a `select`, your second doesn't. Usually you insert the results of the proc into a table and select from it – Nick.Mc Oct 22 '22 at 01:39
  • @Nick.McDermaid - Doesn't work = the front end GUI accepts the 2 parameters, then seems to give up after < 1 second and returns nothing. Despite the above stored proc if called via SSMS taking :15 to run. Other stored procs (incl. my own that I'm trying to replace) work fine with the GUI. Thanks, I"ll have to try inserting into a tmptable once I can get back to my machine. – bbb0777 Oct 22 '22 at 04:05
  • @Nick.McDermaid Thanks, got it working with a tmp table. Posted as answer. – bbb0777 Oct 23 '22 at 18:15

2 Answers2

1

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
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
1

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
bbb0777
  • 165
  • 14