USP_DATAFORMTEMPLATE_EDITLOAD_MOVEDESIGNATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MOVEDESIGNATION (
  @ID uniqueidentifier
  ,@DATALOADED bit = 0 output
  ,@TSLONG bigint = 0 output
  ,@PARENTDESIGNATIONID uniqueidentifier = null output
  ,@PURPOSELOCATION tinyint = null output
  ,@MAXIMUMLEVEL int = null output
  )
as
set nocount on;
set @DATALOADED = 0;
set @TSLONG = 0;
set @PURPOSELOCATION = 0;

declare @OLDLEVEL int;
declare @OLDPATH nvarchar(200);

set @MAXIMUMLEVEL = 0;

select @DATALOADED = 1
  ,@TSLONG = TSLONG
  ,@PARENTDESIGNATIONID = P.ID
  ,@OLDPATH = C.PATHID
  ,@OLDLEVEL = C.level
from dbo.DESIGNATION D
inner join dbo.UFN_DESIGNATION_BUILDNAME_BULK() C on D.ID = C.ID
left join dbo.UFN_DESIGNATION_BUILDNAME_BULK() P on C.PARENTPATHID = P.PATHID
where D.ID = @ID;

if @DATALOADED = 1
begin
  select @MAXIMUMLEVEL = 4 - (COALESCE(MAX([LEVEL]), @OLDLEVEL) - @OLDLEVEL)
  from dbo.UFN_DESIGNATION_BUILDNAME_BULK()
  where PATHID like @OLDPATH + '%'
end

if @PARENTDESIGNATIONID is not null
  set @PURPOSELOCATION = 1;

return 0;