/* */
insert into DAF_Global.dbo.wasp_log values (getdate(),
@agency_code, @user_id, @user_old, @ency_old, '',
'','','','','NOT PROCESSED')

update DAF_Global.dbo.wasp_log set user_status = 'USER NOT FOUND',
process_status = ''
where log_id = @log_id

/* */
delete DAF_Global.dbo.adm_app where user_id = @user_id and agency_code = @agency_code

update DAF_Global.dbo.wasp_log set user_status = 'TERMINATION',
process_status = 'adm_app DELETED'
where log_id = @log_id

/* */

update DAF_Global.dbo.wasp_log set new_first = @first_name where log_id = @log_id

update DAF_Global.dbo.adm_user set first_name = @first_name,
last_name = @last_name,
user_name_enc = @ency_new
where user_id = @user_id


update DAF_Global.dbo.wasp_log set new_user = @user_new,
new_ency = @ency_new,
user_status = 'NAME CHANGED',
process_status = 'adm_user UPDATED'
where log_id = @log_id

quote:

---

Holy crap!! Problem solved!!

---

quote:

---

...how the task I've described can be done more effectively and in a set-based manner I'd be more than happy to supply you with sample data and explain the procedure.

---

insert into wasp_proc values
('T514XXXXXXX01JASON JONES-514')
insert into wasp_proc values
('T513XXXXXXX02LYNELL JONES-513')
insert into wasp_proc values
('CO340XXXXXXX03JENNIFER JONES-340')
insert into wasp_proc values
('CF340XXXXXXX03JENNIFER')
insert into wasp_proc values
('CL340XXXXXXX03SMITH')
insert into wasp_proc values
('CU340XXXXXXX03JENNIFER SMITH-340')
insert into wasp_proc values
('T514XXXXXXX04BEVERLY JONES-514')
insert into wasp_proc values
('CO290XXXXXXX05COIT JONES-290')
insert into wasp_proc values
('CF290XXXXXXX05COIT')
insert into wasp_proc values
('CL290XXXXXXX05SMITH')
insert into wasp_proc values
('CU290XXXXXXX05COIT SMITH-290')
insert into wasp_proc values
('T512XXXXXXX06LORI JONES-512')
insert into wasp_proc values
('T513XXXXXXX07BARBARA JONES-513')
insert into wasp_proc values
('CO290XXXXXXX08BRYAN JONES-290')
insert into wasp_proc values
('CF290XXXXXXX08BRYAN')
insert into wasp_proc values
('CL290XXXXXXX08JONES')
insert into wasp_proc values
('CU290XXXXXXX08null')
insert into wasp_proc values
('T514XXXXXXX09JENNIFER SMITH-514')

create table #wasp_proc
       (ssn char(9)
       ,enc char(3)
       ,isTerminated bit
       ,firstname varchar(75)
       ,lastname varchar(75)
       ,user_name_enc varchar(75)
       ,[user] varchar(75))

;with 
offset (w_id, offset, w_data) as
(       
       select w_id
              ,case when left(w_data,1) = 'T' then 0 else 1 end
              ,w_data
       from   wasp_proc 
)
,parse (w_id, [action],[enc],[ssn],[value]) as
(
       select w_id
              ,substring(w_data, 1, 1+offset)
              ,substring(w_data, 2+offset, 3)
              ,substring(w_data, 5+offset, 9)
              ,substring(w_data, 14+offset, 75)
       from   offset 
)
,final (ssn,enc,isTerminated,firstname,lastname,[user]) as
(
       select ssn
              ,max(enc) enc
              ,max(case when action = 'T' then 1 else 0 end) isTerminated
              ,max(case 
                     when action = 'CF' then [value] 
                     when action = 'T' then substring([value],1, isNull(nullif(charindex(' ', [value]),0),75)-1)
                     end) FirstName
              ,max(case 
                     when action = 'CL' then [value] 
                     when action = 'T' then replace(substring([value],isNull(nullif(charindex(' ', [value]),0)+1,1), 75),'-'+enc,'')
                     end) LastName
              ,max(case when action in ('T','CO') then [value] end) as [user]
       from parse
       group by ssn
)
insert #wasp_proc
       (ssn
       ,enc
       ,isTerminated
       ,firstname
       ,lastname 
       ,user_name_enc
       ,[user])
select ssn
       ,enc
       ,isTerminated
       ,firstname
       ,lastname 
       ,enc + left(firstname,1) + lastname + 'ENCY' as user_name_enc
       ,[user]
from final

select t.*
       ,au.*
from   #wasp_proc t
left outer join adm_user au on au.user_name_enc = t.user_name_enc

insert into wasp_log
    (
    <column List>
    )
select
    <columns and expressions>

	case 
		when au.user_name_enc is null then 'USER NOT FOUND'
		when t.isTerminated = 1 then 'TERMINATION'
		when t.isTerminated = 0 and au.user_id is not null then 'NOT PROCESSED'
		etc...
		end,
	''
from #wasp_proc t
left outer join adm_user au on au.user_name_enc = t.user_name_enc

create table #wasp_proc
       (ssn char(9)
       ,agency char(3)
       ,isTerminated bit
       ,firstname varchar(75)
       ,lastname varchar(75)
       ,user_name_enc varchar(32)
       ,[user] varchar(75)
       ,new_user_name_enc varchar(32))

;with 
offset (w_id, offset, w_data) as
(       
       select w_id
              ,case when left(w_data,1) = 'T' then 0 else 1 end
              ,w_data
       from   wasp_proc 
)
,parse (w_id, [action],[agency],[ssn],[value]) as
(
       select w_id
              ,substring(w_data, 1, 1+offset)
              ,substring(w_data, 2+offset, 3)
              ,substring(w_data, 5+offset, 9)
              ,substring(w_data, 14+offset, 75)
       from   offset 
)
,final (ssn,agency,isTerminated,firstname,lastname,[user]) as
(
       select ssn
              ,max(agency) agency
              ,max(case when action = 'T' then 1 else 0 end) isTerminated
              ,max(case 
                     when action = 'CF' then [value] 
                     when action = 'T' then substring([value],1, isNull(nullif(charindex(' ', [value]),0),75)-1)
                     end) FirstName
              ,max(case 
                     when action = 'CL' then [value] 
                     when action = 'T' then replace(substring([value],isNull(nullif(charindex(' ', [value]),0)+1,1), 75),'-'+agency,'')
                     end) LastName
              ,max(case when action in ('T','CO') then [value] end) as [user]
       from parse
       group by ssn
)
insert #wasp_proc
       (ssn
       ,agency
       ,isTerminated
       ,firstname
       ,lastname 
       ,user_name_enc
       ,[user]
       ,new_user_name_enc)
select ssn
       ,agency
       ,isTerminated
       ,firstname
       ,lastname 
       ,dbo.fn_enc([user]) as user_name_enc
       ,[user]
       ,dbo.fn_enc(rtrim(firstname)+' '+RTRIM(lastname)+'-'+agency) as new_user_name_enc
from final

insert into wasp_log
	(log_date
	,agency_code
	,user_id
	,old_user
	,old_ency
	,new_first
	,new_last
	,new_user
	,new_ency
	,user_status
	,process_status)
select 
	GETDATE(),
	t.agency,
	au.user_id,
	t.[user],
	t.user_name_enc,
	t.firstname,
	t.lastname,
	rtrim(t.firstname)+' '+rtrim(t.lastname)+'-'+t.agency,
	t.new_user_name_enc,
	case
		when au.user_id is null then 'USER NOT FOUND'
		when t.isTerminated = 1 and au.user_id is not null then 'TERMINATION'
		when t.isTerminated = 0 and au.user_id is not null
			and t.new_user_name_enc <> au.user_name_enc then 'NAME CHANGED'
		else ''
	end,
	case when t.isTerminated = 1 and au.user_id is not null then 'adm_app DELETED'
		 when t.isTerminated = 0 and au.user_id is not null
			and t.new_user_name_enc = au.user_name_enc then 'NOT PROCESSED'
		 when t.isTerminated = 0 and au.user_id is not null
			and t.new_user_name_enc <> au.user_name_enc then 'adm_user UPDATED'
		 else ''
	end		
from   #wasp_proc t
left outer join adm_user au on au.user_name_enc = t.user_name_enc

delete adm_app where user_id in
(select au.user_id
from   #wasp_proc t
left outer join adm_user au on au.user_name_enc = t.user_name_enc
where t.isTerminated = 1 and au.user_id is not null)

update PCard.dbo.pcard_user
   set first_name = t.firstname,
       last_name = t.lastname,
       user_name = RTRIM(t.firstname)+' '+RTRIM(t.lastname)
   from #wasp_proc t
  left outer join adm_user au on au.user_name_enc = t.user_name_enc
    where t.isTerminated = 0 and au.user_id is not null and au.user_name_enc = t.user_name_enc
  and t.new_user_name_enc <> au.user_name_enc
  and au.user_id = pcard_user.user_id
  
  update Pmtsvcs.dbo.pmtsvcs_user
   set first_name = t.firstname,
       last_name = t.lastname,
       user_name = RTRIM(t.firstname)+' '+RTRIM(t.lastname)
   from #wasp_proc t
  left outer join adm_user au on au.user_name_enc = t.user_name_enc
    where t.isTerminated = 0 and au.user_id is not null and au.user_name_enc = t.user_name_enc
  and t.new_user_name_enc <> au.user_name_enc
    and au.user_id = pmtsvcs_user.user_id

update adm_user
  set first_name = t.firstname,
      last_name = t.lastname,
      user_name_enc = dbo.fn_md5(RTRIM(t.firstname)+' '+rtrim(t.lastname)+'-'+t.agency)
  from #wasp_proc t
  left outer join adm_user_test au on au.user_name_enc = t.user_name_enc
  where t.isTerminated = 0 and au.user_id is not null and au.user_name_enc = t.user_name_enc
  and t.new_user_name_enc <> au.user_name_enc

drop table #wasp_proc