openrowset | Advanced Openrowset

--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
Comments