[DB] Factorial SP, UDF

개발자

[DB] Factorial SP, UDF

zupper 2009. 11. 30. 15:07

-- Factorial UDF
-- Parent로 Level을 return (Field에 Level 값이 없을 경우 가상으로 만들어 줌)

[1] OC_PARENT 를 파라미터로 넘길것이다.
[2] OC_PARENT 의 값이 0 일때는 0을 반환
[3] OC_PARENT 의 값이 0 이 아닐때는 FACTORIAL 함수를 통해 0이 나올때까지 재귀

[ZUPPER's Code]
Stored Procedure
-- Excute /* DECLARE @ANSWER INT EXEC @ANSWER = MTGi_FN_GET_MSP_TOP_OC_PARENT_BY_CODE_UID 544 SELECT @ANSWER EXEC @ANSWER = MTGi_FN_GET_MSP_TOP_OC_PARENT_BY_CODE_UID 397 SELECT @ANSWER EXEC @ANSWER = MTGi_FN_GET_MSP_TOP_OC_PARENT_BY_CODE_UID 381 SELECT @ANSWER */ ALTER PROC MTGI_SP_GET_MSP_OC_LEVEL_BY_CODE_UID @OC_CODE INT, @OC_LEVEL INT AS SET NOCOUNT ON DECLARE @RESULT_PARENT INT SET @RESULT_PARENT = 0 BEGIN DECLARE @PROJ_ID INT SELECT @PROJ_ID = PROJ_ID FROM MSP_PROJECTS WHERE PROJ_ID > 0 AND PROJ_TYPE = 2 SELECT @RESULT_PARENT = ISNULL(OC_PARENT,-1) FROM MSP_OUTLINE_CODES WHERE CODE_UID = @OC_CODE AND RESERVED_DATA = 0 AND PROJ_ID = @PROJ_ID AND CODE_UID > 0 IF (@RESULT_PARENT > 0) BEGIN SET @OC_LEVEL = @OC_LEVEL + 1 -- PRINT ' MTGI_SP_GET_MSP_OC_LEVEL_BY_CODE_UID '+ CONVERT(NVARCHAR,@RESULT_PARENT) +','+CONVERT(NVARCHAR,@OC_LEVEL) EXEC @OC_LEVEL = MTGI_SP_GET_MSP_OC_LEVEL_BY_CODE_UID @RESULT_PARENT, @OC_LEVEL END ELSE BEGIN SET @OC_CODE = 0 -- PRINT '--->@OC_LEVEL='+CONVERT(NVARCHAR,@OC_LEVEL) END IF(@@ERROR = 0) RETURN @OC_LEVEL ELSE RETURN -1 END

Function

저작자표시 (새창열림)