May 21, 2008 at 7:07 am
I'm trying to eliminate using a cursor in a stored procedure, but I'm having trouble getting the desired results.
Any and all help is greatly appreciated!
(Using SS2K5)
Thanks,
Wayne
-- create the table structures
declare @T table (
DataColumn1 numeric(4),
DataColumn2 varchar(15),
DataColumn3 varchar(13),
DataColumn4 varchar(80),
Worker varchar(500),
DataColumn6 char(1),
DataColumn7 varchar(50),
MaintenanceID integer)
declare @IMP table (
MaintenanceID integer,
SequenceID tinyint,
PersonID integer)
declare @P table (
PersonID integer,
FirstName varchar(30),
LastName varchar(30))
-- put some test data into the tables
insert into @T
select 1, 'AB8461', '1111223334444', 'Description of Data 1', '', 'Y', NULL, 1 UNION
select 2, 'NBI4659841', '2222334445555', 'Description of Data 2', '', 'N', NULL, 2
insert into @IMP
select 1, 1, 1 UNION
select 1, 2, 2 UNION
select 2, 1, 2 UNION
select 2, 2, 1
insert into @P
select 1, 'MyFirstFirstName', 'MyFirstLastName' UNION
select 2, 'MySecondFirstName', 'MySecondLastName'
-- need to make @T.Workers = @CRLF delimited list of all people in @P associated with @T.MaintenanceID through @IMP
-- needs to be in descending SequenceID order
/*
============================================================
TODO: need to make this routine set based, vs. cursor based.
============================================================
*/
declare @CRLF char(2)
declare @MaintenanceID integer
declare @Workers varchar(500)
set @CRLF = char(13) + char(10)
declare cMaint cursor for
select distinct MaintenanceID from @T
open cMaint
fetch next from cMaint into @MaintenanceID
while @@fetch_status = 0 begin
set @Workers = ''
select @Workers = @Workers + case when @Workers > '' then @CRLF else '' end + p.LastName + ', ' + p.FirstName
from @IMP imp
INNER JOIN @P p ON imp.PersonID = p.PersonID
where imp.MaintenanceID = @MaintenanceID
order by imp.SequenceID DESC
update @T set Worker = @Workers where MaintenanceID = @MaintenanceID
fetch next from cMaint into @MaintenanceID
end
close cMaint
deallocate cMaint
-- show the results
select * from @T
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 21, 2008 at 1:31 pm
I ran your test code, and I think what you're trying to do can be done using the Running Totals code from [ulr]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]. Instead of a running total, you build a string concatenation, of course, but the basic concept should work, and should be much faster than a cursor.
The other way to do it would be with a recursive CTE. Try the running totals code first, and if that doesn't work, let me know and I'll look at helping you with a CTE.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 1:42 pm
WayneS (5/21/2008)
I'm trying to eliminate using a cursor in a stored procedure, but I'm having trouble getting the desired results.Any and all help is greatly appreciated!
(Using SS2K5)
Thanks,
Wayne
XML processing to the rescue.
-- create the table structures
declare @T table (
DataColumn1 numeric(4),
DataColumn2 varchar(15),
DataColumn3 varchar(13),
DataColumn4 varchar(80),
Worker varchar(500),
DataColumn6 char(1),
DataColumn7 varchar(50),
MaintenanceID integer)
declare @IMP table (
MaintenanceID integer,
SequenceID tinyint,
PersonID integer)
declare @P table (
PersonID integer,
FirstName varchar(30),
LastName varchar(30))
-- put some test data into the tables
insert into @T
select 1, 'AB8461', '1111223334444', 'Description of Data 1', '', 'Y', NULL, 1 UNION
select 2, 'NBI4659841', '2222334445555', 'Description of Data 2', '', 'N', NULL, 2
insert into @IMP
select 1, 1, 1 UNION
select 1, 2, 3 UNION
select 2, 1, 2 UNION
select 2, 2, 1 UNION
select 2, 1, 4
insert into @P
select 1, 'tony', 'stark' UNION
select 2, 'peter', 'parker' union
select 3, 'clark','kent' union
select 4, 'bruce','wayne'
declare @folks table (MaintenanceID int, Worker varchar(max))
insert into @folks
select M.MaintenanceID,
stuff( (select '|'+p.LastName + ', '+ p.FirstName
from @P p join @imp imp on
imp.PersonID = p.PersonID
where imp.MaintenanceID = M.MaintenanceID
order by p.LastName, p.FirstName
for xml path('')),
1,1,'') as worker
from
(select distinct MaintenanceID from @imp) as M
select * from @folks
select * From @T
update T
set T.Worker = replace(F.worker,'|',char(13) + char(10))
from @T as T join @folks F on F.MaintenanceID = T.MaintenanceID
select * From @T
I had to change your person names since they were confusing.
Make sure your Worker column can accomodate the data. varchar(500) could be shorter than you think if many folks are associated to a MaintenanceID.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply