How to write sql for document approval process

caoyuezj

How to delete it?

lan__cat

It should be very simple to write this stored procedure in CLR and C #

toogun

create procedure p_SpIsExist (@vDjlxbm varchar (3), @vSpInfo varchar (8000), @vBm varchar (30), @iIsExist int output)
as
begin
  select @ iIsExist = count (*) from tbnote_check_order where charindex ('| bmbegin |' + @ vBm + '| bmend |', @vSpInfo)> 0 and f_bmbm = @ vBm and f_djlxbm = @ vDjlxbm
end

create function fun_getspinfo (@vSpInfo varchar (8000))
returns varchar (8000)
as
begin
  return (isnull (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (@PLACESpline, '| linebegin |', ''), '| lineend |', ''), '| bmbegin |', ''), ' | bmend | ',' '),' | ygbmbegin | ',' '),' | ygbmend | ',' '),' '))
end

create function fun_DelBmSpyj (@vSpInfo varchar (8000), @ vBm varchar (30))
returns varchar (8000)
as
begin
   declare @w int
   declare @len int
   declare @line varchar (8000)
   declare @s varchar (8000)
   set @ len = len (@vSpInfo)
   if @len> 0
   begin
      set @ s = @ vSpInfo
      while @len> 1
      begin
          if substring (right (@ vSpInfo, @ len), 1, len ('| linebegin |')) = '| linebegin |'
          begin
            set @ line = substring (@ vSpInfo, len (@vSpInfo)-@ len + 1, charindex ('| lineend |', right (@ vSpInfo, @ len)) + len ('| lineend |')-1)
            set @ w = charindex ('| bmbegin |' + @ vBm + '| bmend |', @ line)
            if @w> 0 set @ s = REPLACE (@ vSpInfo, @ line, '')
          end
          set @ len = @ len-1
      end
   end
   else set @ s = @ vSpInfo
   return isnull (@s, '')
end

--test
declare @s varchar (8000)
set @ s = 'fdssdf | linebegin | haha2007-1-1 | bmbegin | 001 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjklsd | lineend | 123'
select dbo.fun_DelBmSpyj (@ s, '001')
--result
fdssdf123

toogun

declare @s varchar (8000)
set @ s = 'fdssdf | linebegin | haha2007-1-1 | bmbegin | 001 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjklsd | lineend | 123'
select dbo.fun_DelBmSpyj (@ s, '004')

--result
fdssdf | linebegin | haha2007-1-1 | bmbegin | 001 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjklsd | lineend | 123

waxiaoning

Don't quite understand the meaning of LZ
Multi-sector approval flow? ? ?

waxiaoning

Think it's time to redesign

caoyuezj

Yes, multi-department approval flow, all approval information is placed in a field, and this field is parsed

caoyuezj

Is there a good way for everyone

caoyuezj

My design is carried out through separators, which can simplify the design and achieve universality and scalability, but it is troublesome when parsing.

shajiachen

Workflow project recommendations refer to wfmc or bpel specifications.