SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: Passing List to Stored Procedure
-
Jun 8, 2004, 07:02 #1
- Join Date
- Apr 2004
- Location
- 13th Floor
- Posts
- 13
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Passing List to Stored Procedure
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
-
Jun 8, 2004, 11:19 #2
- Join Date
- Nov 2002
- Location
- UK, soon USA
- Posts
- 315
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jun 8, 2004, 16:43 #3
- Join Date
- Jan 2002
- Location
- London
- Posts
- 3,509
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
MarcusJT
- former ASP web developer / former SPF "ASP Guru"
- *very* old blog with some useful ASP code
- Please think, Google, and search these forums before posting!
-
Jun 9, 2004, 06:37 #4
- Join Date
- Nov 2002
- Location
- UK, soon USA
- Posts
- 315
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
=o)
Bookmarks