Help Validating My Query

[fooobee]

My Task..I have to remove all "accounts" beginning with 00, 01, or 02 from the "allowedAccounts" column.

However, multiple accounts exists for one record. The database was not designed properly. Yes, we are in the process of re-designing it, but for the next 3 months we are using this..

example of one record.

CompanyID varchar(50) allowedAccounts (varchar 8000)
--- ----------------
001 00_Rep234, 01_Rep393, 03_Rep59

I put together some code and I just want to have someone validate it. It seems to work, but it may not.

--Delete CompanyID Is NUll
Delete CompanySubscriptionReports where CompanyID is NUll

--Delete Duplicates rows
select distinct *
into holding
from CompanySubscriptionReports
truncate table CompanySubscriptionReports
insert CompanySubscriptionReports
select *
from holding
drop table holding

--Create normalized table to Append Data
create table ReportSubscription (
	CompanyID int not null,
	Reports varchar(200) null)

--Need Tally Table
create table Tally (
    ID int not null)
   
go

set nocount on
declare @i int
select  @i = 0
while @i <5000
begin
    insert into Tally (ID)
    select (1 + @i)  
    select @i = @i + 1
end
go

--Insert Data to Normalized Table
INSERT INTO ReportSubscription SELECT CompanyID, 
NullIf(SubString(',' + allowedAccounts + ',' , ID , CharIndex(',' , ',' + allowedAccounts + ',' , ID) - ID) , '') AS Report 
FROM Tally, CompanySubscriptionReports 
WHERE ID <= Len(',' + allowedAccounts + ',') AND SubString(',' + allowedAccounts + ',' , ID - 1, 1) = ','

--Delete Duplicate Report ID from ReportSubscription
select distinct *
into holding
from ReportSubscription
truncate table ReportSubscription
insert ReportSubscription
select *
from holding
drop table holding

--Delete Reports From 00, 01, 02 and Delete Null
Delete ReportSubscription Where Left(Reports,2) in ('00','01','02')
Delete ReportSubscription where Reports is null

--Truncate CompanySubscriptionReports
truncate table CompanySubscriptionReports

--create a table to work with
create table workingtable (
    CompanyID int not null,
    Reports varchar(200) not null,
    list varchar(7000),
    constraint pk_wt primary key (CompanyID,Reports) )

insert into workingtable (CompanyID ,Reports)
select CompanyID ,Reports
from ReportSubscription
order by CompanyID ,Reports

declare
    @list varchar(7000),
    @lasti int

select
    @list = '',
    @lasti = -1

--here is the meat of the work
update
    workingtable
set
    @list = list = case
                       when @lasti <> CompanyID  then Reports
                       else @list + ', ' + Reports
                   end,
    @lasti = CompanyID 

--Insert into CompanySubscription
INSERT INTO CompanySubscriptionReports ( CompanyID, allowedAccounts )
select
    CompanyID ,
    case
        when len(max(list)) > 7000 then convert(varchar(7000), left(max(list),6700))
        else convert(varchar(7000),max(list))
    end as list
from
    workingtable
group by
    CompanyID 
order by
    newid()
go

--Remove Unused tables
drop table workingtable
drop table ReportSubscription
drop table Tally

 

[vongrunt]

I'm not sure this will work as expected if allowedAccounts has some blanks after comma characters (, ). Simple LTRIM() can fix that minor problem though.

Other than that... very interesting piece of code.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)