[MsSql] hierarchyid 를 사용한 계층구조 검색

/****** Object:  StoredProcedure [dbo].[SPP_ResultTailInfo_Tree]    Script Date: 2016-07-01 오전 10:27:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  jwcho
-- Create date: 2016.06.22
-- Modify date: 2016.07.01
-- =============================================
ALTER PROCEDURE [dbo].[SPP_ResultTailInfo_Tree]
 @V_PRJ_ID VARCHAR(50)=NULL,   -- Project ID
 @V_HR_ID VARCHAR(50)=NULL,   -- 상위명칭
 @V_FLDNAME VARCHAR(50)=NULL,  -- 검색할 필드명
 @V_SIGN VARCHAR(20)=NULL,   -- 비교문(=, like, ...)
 @V_VALUE VARCHAR(50)=NULL,   -- 검색문자열
 @V_FLDNAME2 VARCHAR(50)=NULL,  -- 비교해야할 필드명
 @V_SIGN2 VARCHAR(20)=NULL,   -- 연산자(=, >, <, >=, <=, <>, like)
 @V_VALUE2 VARCHAR(1024)=NULL,  -- 비교해야할 값
 @V_ORDER VARCHAR(50)=NULL, -- 정렬칼럼
 @V_HISTORY INT=0, -- 변동기록보기
 @V_DEBUG INT=0
AS
BEGIN
 SET NOCOUNT ON;

 declare @v_sql varchar(max);
 declare @sign varchar(20)='=';
 declare @hid hierarchyid;

 ---------------------------------------------------------------------------
 -- 매개변수 오류 체크
 if dbo.FN_IsNull(@V_PRJ_ID)=1
 begin
  set @v_sql='select [HID],HID.GetLevel() HidLevel,[PRJ_ID],[ID],[HR_ID],[HR_NAME],[NAME],[SPEC],[GUBUN]'+char(13);
  set @v_sql=@v_sql+' ,[ZONE],[DONG],[FLOOR],[HOUSEHOLD],[ROOM],[PYUNG],[LINE],[MOF],[SYMBOL],[CONSTRUCTION],[CUST_ID],[COST_ID]'+char(13);
  set @v_sql=@v_sql+' ,[COUNT],[MID],[QTY],[UNIT],[FORMULA],[MUNITCOST],[MCOST],[LUNITCOST],[LCOST],[EUNITCOST],[ECOST]'+char(13);
  set @v_sql=@v_sql+' ,[X],[Y],[H],[G],[S],[SM],[V],[REMARK],[ORDER],[REF_ID],[RESERVE1],[RESERVE2],[YN],[YN_DATE],[REG_DATE],[CDATE]'+char(13);
  set @v_sql=@v_sql+'from dbo.TBP_ResultTailInfo'+char(13);
  --set @v_sql=@v_sql+'where 1=1 and [PRJ_ID]=''____NONE___'''+char(13);

  if @V_DEBUG=0
   execute (@v_sql);
  else
   print @v_sql;
  return
 end
 ---------------------------------------------------------------------------
 
 ---------------------------------------------------------------------------
 -- 상위코드 검색
 if dbo.FN_IsNull(@V_HR_ID)=0
  select top 1 @hid=[HID] from TBP_ResultTailInfo where YN='N' and [PRJ_ID]=@V_PRJ_ID and [ID]=@V_HR_ID;
 ---------------------------------------------------------------------------

 set @v_sql='select [HID],HID.GetLevel() HidLevel,[PRJ_ID],[ID],[HR_ID],[HR_NAME],[NAME],[SPEC],[GUBUN]'+char(13);
 set @v_sql=@v_sql+' ,[ZONE],[DONG],[FLOOR],[HOUSEHOLD],[ROOM],[PYUNG],[LINE],[MOF],[SYMBOL],[CONSTRUCTION],[CUST_ID],[COST_ID]'+char(13);
 set @v_sql=@v_sql+' ,[COUNT],[MID],[QTY],[UNIT],[FORMULA],[MUNITCOST],[MCOST],[LUNITCOST],[LCOST],[EUNITCOST],[ECOST]'+char(13);
 set @v_sql=@v_sql+' ,[X],[Y],[H],[G],[S],[SM],[V],[REMARK],[ORDER],[REF_ID],[RESERVE1],[RESERVE2],[YN],[YN_DATE],[REG_DATE],[CDATE]'+char(13);
 set @v_sql=@v_sql+'from TBP_ResultTailInfo'+char(13);
 set @v_sql=@v_sql+'where 1=1'+char(13);
 
 if @hid is not null
  set @v_sql=@v_sql+' and [HID].ToString() like '''+@hid.ToString()+'%'''+char(13);

 if dbo.FN_IsNull(@V_SIGN)=0
  set @sign = @V_SIGN;
 else
  set @sign ='=';
  
 if dbo.FN_IsNull(@V_FLDNAME)=0
  set @v_sql = @v_sql+'  and '+@V_FLDNAME+' '+@sign+' '+(case when dbo.FN_IsNull(@V_VALUE)=1 then 'null' else ''''+@V_VALUE+'''' end)+char(13);

 if dbo.FN_IsNull(@V_SIGN2)=0
  set @sign = @V_SIGN2;
 else
  set @sign ='=';
  
 if dbo.FN_IsNull(@V_FLDNAME2)=0
  set @v_sql = @v_sql+'  and '+@V_FLDNAME2+' '+@sign+' '+(case when dbo.FN_IsNull(@V_VALUE2)=1 then 'null' else ''''+@V_VALUE2+'''' end)+char(13);

 if dbo.FN_IsNull(@V_ORDER)=0
  set @v_sql=@v_sql+'order by '+@V_ORDER+char(13);

 if @V_DEBUG=0
  execute (@v_sql);
 else
  print @v_sql;

END