CREATE PROCEDURE PRO_FLOW
@OA_ID varchar(20),
@DO_NO varchar(20),
@STATUS varchar(500),
@IsFINISH varchar(1)
as
Declare @Str NVARCHAR(100)
declare @splitchar char(1)
declare @I int = 0
declare @S int = 0
declare @Sql NVARCHAR(500)
declare @index int=1
PRINT(@STATUS)
SET @Sql = 'UPDATE ADMIN_DB..OA_FLOW SET SYS_DATE = GETDATE(),STATUS = '''+@STATUS+''''
PRINT(@Sql)
SET @splitchar=','
SET @Sql = @Sql+',FINSIGH' + '=''' + @IsFinish+''''
IF(@IsFinish='Y')
BEGIN
WHILE (@I<=LEN(@STATUS))
BEGIN
declare @f nvarchar(50)
SET @S = CHARINDEX(@splitchar,@STATUS,@I)
IF(@S=0)
BEGIN
SET @S = LEN(@STATUS)+1
END
IF(@I=0)
BEGIN
set @f= SUBSTRING(@STATUS,@I,@S)
END
ELSE
BEGIN
set @f= SUBSTRING(@STATUS,@I,@S-@I)
END
PRINT(@f)
SET @I = @S+1
PRINT(@I)
DECLARE @D INT
SET @D = CHARINDEX('/',@f,0)
SET @Sql = @Sql+',F'+CONVERT(varchar(10),@index) + '='''+ CONVERT(varchar(10),@index)+''',F'+CONVERT(varchar(10),@index) +'_CONF=''Y''' +',F'+CONVERT(varchar(10),@index) +'_USER=''' + SUBSTRING(@f,0,@D)+''',F'+CONVERT(varchar(10),@index) +'_DATE='''+ SUBSTRING(@f,@D+1,LEN(@f)-@D-1)+''''
SET @index = @index + 1
END
END
SET @Sql = @Sql + ' WHERE DO_NO='''+@DO_NO +''''
PRINT(@Sql)
EXEC(@Sql)
GO