MSSQL 요척 조회 함수

USE [skoolooks_erp]
GO
/****** Object:  UserDefinedFunction [dbo].[FN_GET_TCODE_YOCHUK]    Script Date: 08/23/2011 17:31:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,yskim>
-- Create date: <Create Date, ,20110804>
-- Description: <Description, , >

-- 요척계산 예외 (2011-08-22)
-- 1. 다음 조건을 만족할 경우
-- A. 복종이 스커트(S)
-- B. TCODE03TT.S_CODE_NAME='앞판'
-- C. TCODE01TT.CODE_NAME='주름종류' && TCODE01TT.CODE_NAME='주름수'
--
-- 2. TORDR02TT.P_CODE_VALUE1 값은 '주름종류'와 '주름수'에 해당하는 요척 값은 TGRUP01TT.YOCHUK 조회
-- A. TGRUP01TT.P_CODE1 = '주름종류'의 P_CODE_VALUE1
-- B. TGRUP01TT.P_CODE2 = '주름수'의 P_CODE_VALUE1

-- 3. 1의 조건에 해당하지 않거나 TORDR02TT.P_CODE_VALUE2 값은 기존 프로세스로 YOCHUK 값을 산정
-- =============================================
ALTER FUNCTION [dbo].[FN_GET_TCODE_YOCHUK]
(
@V_GUBUN VARCHAR(20)
, @V_YEAR VARCHAR(8)
, @V_SEASON VARCHAR(2)
, @V_SCHOOL VARCHAR(10)
, @V_BOKJONG VARCHAR(2)
, @V_PUMOK VARCHAR(2)
, @V_SAYANG VARCHAR(2)
, @V_CHASU VARCHAR(2)
)
RETURNS DECIMAL(3,2)
AS
BEGIN
-- Declare the return variable here
--DECLARE @ResultVar VARCHAR(4000)
DECLARE @ResultVar DECIMAL(3,2) = 0.00
DECLARE @PYochuk1 DECIMAL(3,2) = 0.00
DECLARE @PYochuk2 DECIMAL(3,2) = 0.00
DECLARE @PPCodeValue1 VARCHAR(100)
DECLARE @PPCodeValue2 VARCHAR(100)
DECLARE @PSCodeName VARCHAR(100)
DECLARE @PCodeName VARCHAR(100)

DECLARE @PCode1 VARCHAR(100) -- TGRUP01TT.P_CODE1
DECLARE @PCode2 VARCHAR(100) -- TGRUP01TT.P_CODE2
DECLARE @PGrupYochuk DECIMAL(3,2) = 0.00 -- TGRUP01TT.YOCHUK

-- Add the T-SQL statements to compute the return value here
DECLARE MY_CURSOR CURSOR FOR
SELECT
YOCHUK1, YOCHUK2
, P_CODE_VALUE1, P_CODE_VALUE2
, S_CODE_NAME, CODE_NAME
FROM (
SELECT
ISNULL((SELECT YOCHUK FROM TCODE02TT X WHERE X.YEAR=T1.YEAR AND X.T_CODE=T2.P_CODE AND X.P_CODE1=T2.P_CODE_VALUE1),0.00) AS YOCHUK1
, ISNULL((SELECT YOCHUK FROM TCODE02TT X WHERE X.YEAR=T1.YEAR AND X.T_CODE=T2.P_CODE AND X.P_CODE2=T2.P_CODE_VALUE2),0.00) AS YOCHUK2
, T2.P_CODE_VALUE1, T2.P_CODE_VALUE2
, T1.BOKJONG
, TC3.S_CODE, TC3.S_CODE_NAME
, TC1.CODE_NAME
, T2.P_CODE
FROM TORDR01TT T1
LEFT OUTER JOIN TORDR02TT T2
ON T1.GUBUN = T2.GUBUN AND T1.YEAR = T2.YEAR AND T1.SEASON = T2.SEASON AND T1.SCHOOL = T2.SCHOOL
AND T1.BOKJONG = T2.BOKJONG AND T1.PUMOK = T2.PUMOK AND T1.SAYANG = T2.SAYANG AND T1.CHASU=T2.CHASU
INNER JOIN TCODE03TT TC3 ON TC3.SEASON=SUBSTRING(T2.P_CODE,1,1) AND TC3.SEX=SUBSTRING(T2.P_CODE,2,1) AND TC3.BOKJONG=SUBSTRING(T2.P_CODE,3,1) AND TC3.S_CODE=SUBSTRING(T2.P_CODE,4,1)
INNER JOIN TCODE01TT TC1 ON TC1.YEAR=T2.YEAR AND TC1.T_CODE=T2.P_CODE
WHERE 1=1
AND T1.GUBUN = @V_GUBUN AND T1.YEAR = @V_YEAR AND T1.SEASON = @V_SEASON AND T1.SCHOOL = @V_SCHOOL
AND T1.BOKJONG = @V_BOKJONG AND T1.PUMOK = @V_PUMOK AND T1.SAYANG = @V_SAYANG AND T1.CHASU = @V_CHASU
) T1

OPEN MY_CURSOR;

FETCH NEXT FROM MY_CURSOR
INTO @PYochuk1, @PYochuk2, @PPCodeValue1, @PPCodeValue2, @PSCodeName, @PCodeName;

WHILE @@FETCH_STATUS = 0
BEGIN

IF @PSCodeName='앞판' AND @PCodeName='주름종류'
BEGIN
SELECT @PCode1 = @PPCodeValue1
END
ELSE IF @PSCodeName='앞판' AND @PCodeName='주름수'
BEGIN
SELECT @PCode2 = @PPCodeValue1
END
ELSE
BEGIN
SELECT @ResultVar = @ResultVar + @PYochuk1
END

SELECT @ResultVar = @ResultVar + @PYochuk2

FETCH NEXT FROM MY_CURSOR
INTO @PYochuk1, @PYochuk2, @PPCodeValue1, @PPCodeValue2, @PSCodeName, @PCodeName;
END;

SELECT @PGrupYochuk = ISNULL(YOCHUK, 0.00) FROM TGRUP01TT T1
WHERE T1.YEAR = @V_YEAR AND T1.BOKJONG = @V_BOKJONG AND P_CODE1=@PCode1 AND P_CODE2=@PCode2

CLOSE MY_CURSOR;
DEALLOCATE MY_CURSOR;

-- Return the result of the function
RETURN @ResultVar;

END