[MSSQL] 사용자 정의 함수(function)에서 변수에 select 한 결과값 대입하기

사용예

use [ponuBIdev];

GO
IF OBJECT_ID (N'dbo.getDeptDepth', N'FN') IS NOT NULL
    DROP FUNCTION getDeptDepth;
GO
CREATE FUNCTION [dbo].[getDeptDepth] (@orgcode varchar(32))
RETURNS int
AS
BEGIN

    DECLARE @depth int;
 SET @depth = 0;

 WHILE (@orgcode IS NOT NULL)
 BEGIN
  SET @orgcode = (SELECT DEPT.PARENT_HR_ORGANIZATION_CODE FROM TB_INFO_DEPT010 as DEPT WHERE DEPT.ORGANIZATION_CODE = @orgcode);
  SET @depth = @depth + 1;
 END

    RETURN @depth;

END
GO

EXEC sp_addextendedproperty N'MS_Description', N'부서코드로 Tree 구조 depth 가져오기', N'Schema', N'dbo', N'FUNCTION', N'getDeptDepth'

 

 

SET @orgcode = (SELECT DEPT.PARENT_HR_ORGANIZATION_CODE FROM TB_INFO_DEPT010 as DEPT WHERE DEPT.ORGANIZATION_CODE = @orgcode);

요런식으로 사용