--We can use the concept showed below to create a proc & exec the proc in an in-line table function!! ------------------------------------------------------------------------------------------ CREATE TABLE TEST.T1( ID INT PRIMARY KEY) INSERT INTO TEST.T1(ID) VALUES (1) CREATE TABLE TEST.T2( ID INT PRIMARY KEY) INSERT INTO TEST.T2(ID) VALUES (1) CREATE PROC TEST.MT1 @ID INT = 1 AS UPDATE TEST.T1 SET ID = @ID CREATE PROC TEST.MT2 @ID INT = 1 AS UPDATE TEST.T2 SET ID = @ID SELECT * FROM TEST.T1 SELECT * FROM TEST.T2 CREATE FUNCTION [OP_GET].[SELECT_FROM_OPENROWSET]( @QRY1 NVARCHAR(MAX), @QRY1PARAM NVARCHAR(MAX) ) RETURNS TABLE AS RETURN ( WITH A (B1) AS ( SELECT B1 FROM OPENROWSET('SQLNCLI', 'Server=.;Uid=sa;Pwd=/''],lp123;Database=TUAN_BIZ_SYSTEM', 'SET NOCOUNT ON; SET FMTONLY OFF; SET XACT_ABORT OFF COMMIT EXEC TEST.MT1 2 SELECT 1 B1') ), B (B2) AS ( SELECT B2 FROM OPENROWSET('SQLNCLI', 'Server=.;Uid=sa2;Pwd=/''],lp123;Database=TUAN_BIZ_SYSTEM', 'SET NOCOUNT ON; SET FMTONLY OFF; SET XACT_ABORT OFF COMMIT EXEC TEST.MT2 2 SELECT 1 B2') ), C AS (SELECT @@SERVERNAME NM) SELECT C.* FROM A INNER JOIN B ON A.B1 <> A.B1 AND B.B2 <> B.B2 RIGHT JOIN C ON C.NM = C.NM ) SELECT * FROM [OP_GET].[SELECT_FROM_OPENROWSET]('', '') SELECT * FROM TEST.T1 SELECT * FROM TEST.T2 |