CODEWITHSUNDEEP
azureazure-sql-database
bermz kastral
bermz kastral

Reputation: 93

SELECT statement FROM sp_execute_remote

Hi can someone help me how could I achieve this. 

  DECLARE @ACTUAL_YEAR INT = 2016
    select DATE_YEAR
    from
            (EXEC sp_execute_remote
            N'MYDATABASE',
            N'SELECT * FROM dbo.MY_TABLE_FUNCTION(@DATE_YEAR)',
            N'@DATE_YEAR INT',
            @ACTUAL_YEAR)

Thanks in advance.

Upvotes: 4

Views: 3753

Answers (1)

Alexander Volok
Alexander Volok

Reputation: 5940

The same approach as in SQL Server:

  • Firstly, the result set of stored procedure to be saved in a temporary table
  • Then manipulation of the resultset by querying that recently created object

Example:

CREATE TABLE #resultset 
(
 Col1.. ColN..
)

DECLARE @ACTUAL_YEAR INT = 2016

INSERT #resultset 
EXEC sp_execute_remote
            N'MYDATABASE',
            N'SELECT * FROM dbo.MY_TABLE_FUNCTION(@DATE_YEAR)',
            N'@DATE_YEAR INT',
            @ACTUAL_YEAR

SELECT DATE_YEAR FROM #resultset

Upvotes: 4

Related Questions