Passing List to Stored Procedure

[Chris3125]

Hi all,

I need to be able to pass a dynamically created list to a stored procedure. I am doing this with SQL 2000 and CF 5.0.

The @SegmentKey will contain the list values. There will be a different number of elements in the list. Can someone explain how I get SQL to read a list?

-- Begin SP

ALTER Procedure uspCrossSegmentEmailRemove
AS
-- select top 10 * from segment order by segmentkey desc
declare @tmpSegments table (PrimID INT Identity(1,1), SegmentKey INT)
declare @tmpLog table (SegmentKey int, segName varchar(255), num int)

declare @SegmentKey INT
declare @Num INT
declare @SegName VarChar(64)

insert into @tmpSegments (SegmentKey) Values (@SegmentKey)



declare curSegments cursor
for select SegmentKey
from @tmpSegments
order By PrimID

open curSegments
-- get all the first segment
fetch next from curSegments into @SegmentKey

-- get all the bad email addresses into the temp table
truncate table tmpEmails

while @@Fetch_status = 0
begin
SELECT @SegName = Name FROM Segment WHERE SegmentKey = @SegmentKey

-- Delete Any Emails from the queue that we already have
DELETE FROM EmailQueue
--SELECT @Num = COUNT(*) FROM EmailQueue
WHERE EmailQueueKey IN
(SELECT EmailQueueKey
FROM EmailQueue
INNER JOIN tmpEmails
ON EmailQueue.Address = tmpEmails.Address
WHERE SegmentKey = @SegmentKey
AND Status = 201)


SET @Num = @@rowcount
insert into @tmpLog (SegmentKey, SegName, Num)
values (@SegmentKey, @SegName, @Num)

-- Add every email from this segment into the tmp List
INSERT INTO tmpEmails (address)
SELECT DISTINCT left(Address,128)
FROM EmailQueue
WHERE SegmentKey = @SegmentKey
AND Status = 201
-- union (
-- SELECT DISTINCT left(Address,128)
-- FROM CustomerHistory
-- WHERE SegmentKey = @SegmentKey
-- )
fetch next from curSegments into @SegmentKey
end


close curSegments
deallocate curSegments

select * from @tmpLog

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- End SP

Thanks,
Chris

[Moridin8]

Just at a quick glance without Query Analyzer at my side to help:
It involves a while loop and/or cursor while utilising string parsing functions (eg. SUBSTRING/CHARINDEX). You already have a cursor though (look into cursor syntax, I don't think the one your using is the fastest) and cursors are not known for speed. Maybe a different method would be best?

[MarcusJT]

See this link, which I posted on my blog only the other day...

[Moridin8]

=o)