Brandon Petersen

This was a bit more complicated than the IF statements, but extremely useful.  I was able to

Procedure [dbo].[deleteParticipant]
@ParticipantID int

as

– Declare the variables to store the values returned by FETCH.
DECLARE @GroupID int
DECLARE group_cursor CURSOR FOR
SELECT GroupID FROM Participants
WHERE ParticipantID = @ParticipantID

OPEN county_cursor

– Perform the first fetch and store the values in variables.
– Note: The variables are in the same order as the columns
– in the SELECT statement.
–FETCH NEXT FROM group_cursor
–INTO @GroupID

– Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

IF (SELECT COUNT(*) FROM Participants WHERE GroupID = @GroupID) = 1
BEGIN
DELETE FROM Groups WHERE GroupID = @GroupID
END

– This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM group_cursor
INTO @GroupID
END

CLOSE group_cursor
DEALLOCATE group_cursor

–delete all group affiliations
Delete From  [dbo].[Groups]
WHERE ParticipantID = @ParticipantID

–delete The Participant
Delete From  [dbo].[Participant]
Where ParticipantID =  @ParticipantID

This entry was posted on Wednesday, June 13th, 2007 @ 12:03 pm on the category General.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your site.