SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    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

  2. #2
    <GIR! Unleash the monkey!> Moridin8's Avatar
    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?

  3. #3
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    See this link, which I posted on my blog only the other day...
    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!

  4. #4
    <GIR! Unleash the monkey!> Moridin8's Avatar
    Join Date
    Nov 2002
    Location
    UK, soon USA
    Posts
    315
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    =o)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •