Re: Problem Executing a Stored Proc within a cursor
- From: "Paul" <paulriley@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 2 Oct 2009 14:48:42 +0100
I was plagued with a database in a company I worked for say 6year ago where
every SP had
IF EXISTS (SELECT........)
After months of tracing this was found to be a mojor source of SP
Recompiles???? In fact it was another colleague who identified it and I was
skeptical to say the least, until he proved it to me.
Replacement with Variables and checks for null removed the problem, however
I will never use Exists again, current versions of SQL server may have
solved this but I really do not have time to set such a test up. In my book
its better to be safe than sorry and if there is a cursory chance it will
occur again in anything I code then I will use other methods.
1. Ansi method - Outer Join
2. Temp Table
3. or even the dreaded 'IN'
I may be completely wrong now but we have all had experiences that warn us
from using a thing again?
"Paul" <paulriley@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:e7zWwBPQKHA.4692@xxxxxxxxxxxxxxxxxxxxxxx
But the exists keyword causes recompiles. Not always a bad thing but can
be. Personally I never use it, there are better ways around it.
"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9C94F3DFE4842Yazorman@xxxxxxxxxxxx
mavrick_101 (mavrick101@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
The reason I didn't want to use a query similar to: "INSERT INTO tableA
SELECT xyc FROM table B" is because of primary key issue. TableA may
already have some rows that exist in table B. So The insert fails and
none of the rows are entered from TableB to TableA. I simply want to
ignore the INSERT statement if the row already exists in TableA.
That's what INSERT WHERE NOT EXISTS is for!
I repeat my previous post in case you missed it:
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:
(But Gert-Jan Strik explained that this is because @@fetch_status is
global to the process, so one cursor affects the other.)
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
.
- Follow-Ups:
- Re: Problem Executing a Stored Proc within a cursor
- From: Erland Sommarskog
- Re: Problem Executing a Stored Proc within a cursor
- Prev by Date: Re: Multiple queries for one report
- Next by Date: Re: Contains and Near Question
- Previous by thread: SQL Server as LDAP storage...
- Next by thread: Re: Problem Executing a Stored Proc within a cursor
- Index(es):
Relevant Pages
|
