/**********************************************************************************************
-- Title : [2k] 4-PartName의 Openquery 변경 예(Parameter 있는 경우)
-- Key word : openquery 4partname 파라미터 parameter
**********************************************************************************************/
-- 4-PartName 형식
ALTER PROC up_get_cab_test @IDX INT
AS
SET NOCOUNT ON
SELECT A.PID, A.WKEY, 'JP', B.AD,
CASE LEFT(B.WKEY, 6) WHEN 'JP0000' THEN B.RN
ELSE CASE B.XC WHEN '6' THEN B.FN WHEN '7' THEN B.FN ELSE B.PN END
END KF,
CASE LEFT(B.WKEY, 6) WHEN 'JP0000' THEN B.RD
ELSE CASE B.XC WHEN '6' THEN B.FD WHEN '7' THEN B.FD ELSE B.PD END
END KD,
B.KC, C.IPC, B.TI, 'JP', B.IMG, ISNULL(D.APO, D.AP), A.COMMENT
FROM (SELECT PID, WKEY, COMMENT
FROM TESTSRV.MYTEST.DBO.MYTEST_MYPAT
WHERE PID = @IDX AND DB = 'JP') A,
JP.DBO.JP1 B WITH (NOLOCK),
JP.DBO.JPIPC C WITH (NOLOCK),
JP.DBO.JPAP D WITH (NOLOCK)
WHERE A.WKEY = B.WKEY AND A.WKEY *= C.WKEY AND C.ORD = 1
AND A.WKEY *= D.WKEY AND D.ORD = 1
-- Openquery 적용 예
ALTER PROC up_get_cab_test9 @PID INT
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(4000)
SET @SQL = 'SELECT A.PID, A.WKEY, ''JP'', B.AD '
+ ' , CASE WHEN LEFT(B.WKEY, 6) = ''JP0000'' THEN B.RN '
+ ' ELSE CASE WHEN B.XC = ''6'' THEN B.FN '
+ ' WHEN B.XC = ''7'' THEN B.FN '
+ ' ELSE B.PN '
+ ' END '
+ ' END KF '
+ ' , CASE WHEN LEFT(B.WKEY, 6) = ''JP0000'' THEN B.RD '
+ ' ELSE CASE WHEN B.XC = ''6'' THEN B.FD '
+ ' WHEN B.XC = ''7'' THEN B.FD '
+ ' ELSE B.PD '
+ ' END '
+ ' END KD '
+ ' , B.KC, C.IPC, B.TI, ''JP'', B.IMG, ISNULL(D.APO, D.AP), A.COMMENT '
+ ' FROM (SELECT PID, WKEY, COMMENT '
+ ' FROM OPENQUERY(TESTSRV, ''SELECT PID, WKEY, COMMENT '
+ ' FROM MYTEST.DBO.MYTEST_MYPATWITH (NOLOCK) '
+ ' WHERE PID = ' + CAST(@PID AS VARCHAR(10))
+ ' AND DB = ''''JP'''''''
+ ' )) A '
+ ' , JP.DBO.JP1 B WITH (NOLOCK) '
+ ' , JP.DBO.JPIPC C WITH (NOLOCK) '
+ ' , JP.DBO.JPAP D WITH (NOLOCK) '
+ ' WHERE A.WKEY = B.WKEY AND A.WKEY *= C.WKEY AND C.ORD = 1 '
+ ' AND A.WKEY *= D.WKEY AND D.ORD = 1 '
EXEC (@SQL)