RE: Problem Executing a Stored Proc within a cursor
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 26 Sep 2009 09:07:41 +0000 (UTC)
mavrick_101 (mavrick101@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Ok, here are the stored procs.....
I have changed the names of the tables/columns but the basic logic is the
same.
The reason I didn't want to use
INSERT INTO xyz SELECT......, is that if the destination table may
already have a row and if ONE INSERT Fails (because of the primary key
constraint), all the inserts would fail in this. That is to say if the
select statement was going to bring 1000 rows and the row 5 already
exists then none of the rows are inserted.
So scrap PROCA and do this in PROCB:
INSERT ClubMemberships(ClubId, MemberId, RoleId)
SELECT C.ClubID, E.UserId, C.RoleType
FROM Clubs C
JOIN EntityMemberships E ON C.EntityID = M.EntityID
WHERE NOT EXISTS (SELECT *
FROM ClubMemberships CM
WHERE C.ClubId = M.ClubID
AND E.UserID = M.MemberID)
If you are not acquainted to EXISTS /NOT EXISTS, you should study
this carefully, because they are operators that you have use for
about all the time when you program in SQL.
As for why your code did behave as expected, I don't know, but I note
that you have a strange way of running the cursor loop. This is the
idiom you should use:
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT ....
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO ...
IF @@fetch_status <> 0
BREAK
-- Do stuff
END
DEALLOCATE cur
1) Always make the cursor STATIC, which means that SQL Server will
compute the result set once, and store it in tempdb for the duration
of the cursor. This gives better performance, and saves you from
surprises.
2) Always may the cursor LOCAL (unless you define it in dynamic SQL).
This means that the cursor goes away when the procedure exits.
Else an error can lead to that the cursor is still active when you
call the procedure again.
3) Only use one FETCH statement, so that you don't have two and change
the SELECT in the cursor and only one of the FETCH statements.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Problem Executing a Stored Proc within a cursor
- From: mavrick_101
- RE: Problem Executing a Stored Proc within a cursor
- From: mavrick_101
- Problem Executing a Stored Proc within a cursor
- Prev by Date: Re: view performance deteriorated
- Next by Date: Re: Symettric key
- Previous by thread: RE: Problem Executing a Stored Proc within a cursor
- Next by thread: Re: Problem Executing a Stored Proc within a cursor
- Index(es):
Relevant Pages
|
