rogotData insert procedure

create proc robotDataInsert
(
 @title varchar(50),
 @folderName varchar(50),
 @contents varchar(4000),
 @imgCount tinyint,
 @robotLevel char(2),
 @robotSensor char(5),
 @errCode tinyint output
)
as
begin
 set @errCode = 0
 declare @idx int
 declare @minIdx int
 declare @newIdx int
 declare @maxIdx int

 declare @buildLevel tinyint
 declare @sourceLevel tinyint
 declare @sensorTouch bit
 declare @sensorLight bit
 declare @sensorUltra bit
 declare @sensorSound bit
 declare @sensorMotor bit

 
 set @folderName = ckrobot.ckrobot.blankDelete(@folderName)

 if(exists(select * from robotData))
 begin
  --중복된 이름이 존재할 경우 에러
  if(exists(select * from robotData where ckrobot.ckrobot.blankDelete(title) = ckrobot.ckrobot.blankDelete(@title)))
   set @errCode = 1
  else
  begin
   --중복된 폴더가 존재할 경우 에러
   if(exists(select title from robotData where folderName = @folderName))
    set @errCode = 2  
  end
 end

 if(@errCode = 0)
 begin
  --고유번호 생성하기
  if(not exists(select * from robotData))
   set @idx = 1
  else
  begin
   select @minIdx = min(idx) from robotData
   select @maxIdx = max(idx) from robotData
   select top 1 @newIdx from
   (
    select idx, (ROW_NUMBER() OVER(ORDER BY idx) + @minIdx)-1 as rn
    from robotData
   )tt
   where idx <> rn
   
   if(@newIdx is not null)
    set @idx = @newIdx
   else
    set @idx = @maxIdx+1
  end

  set @buildLevel = substring(@robotLevel, 1, 1)
  set @sourceLevel = substring(@robotLevel, 2, 1)

  set @sensorTouch = substring(@robotSensor, 1, 1)
  set @sensorLight = substring(@robotSensor, 2, 1)
  set @sensorUltra = substring(@robotSensor, 3, 1)
  set @sensorSound = substring(@robotSensor, 4, 1)
  set @sensorMotor = substring(@robotSensor, 5, 1)

  insert into robotData (Idx, title, folderName, contents, imgCount, buildLevel, sourceLevel, sensorTouch, sensorLight, sensorUltra, sensorSound, sensorMotor)
  values (@idx, @title, @folderName, @contents, @imgCount, @buildLevel, @sourceLevel, @sensorTouch, @sensorLight, @sensorUltra, @sensorSound, @sensorMotor)
 end

end
go