Why work this Code without Error?

Question

Post reply

Why work this Code without Error?

info 58414

Hall of Fame

Points: 3386
More actions
June 21, 2014 at 11:50 pm

#287024

Hi,
the Select for the cursor uses the same table as the update statement in the cursor. If the data set by the cursor Reading not be locked for update statement?
Regards
Nicole 😉
DECLARE @pkPerson INT
DECLARE myCur CURSOR
LOCAL
FORWARD_ONLY
FAST_FORWARD
READ_ONLY
TYPE_WARNING
FOR SELECT pkPerson
FROM dbo.tEmployee
OPEN myCur ;
FETCH NEXT FROM myCur INTO @pkPerson
WHILE @@FETCH_STATUS = 0
BEGIN;
UPDATE dbo.tEmployee
SET ActiveRoles = dbo.udf_GetRoles(@pkPerson)
WHERE pkPerson = @pkPerson
FETCH NEXT FROM myCur INTO @pkPerson
END ;
CLOSE myCur ;
DEALLOCATE myCur ;

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

Don't think there is a need for a cursor

😎

UPDATE E

SET E.ActiveRoles = dbo.udf_GetRoles(P.pkPerson)

FROM dbo.tEmployee E

INNER JOIN

P

ON E.pkPerson = P.pkPerson;

The correct cursor code

DECLARE @pkPerson INT

DECLARE myCur CURSOR

FAST_FORWARD FOR

SELECT pkPerson

FROM dbo.tEmployee

OPEN myCur ;

FETCH NEXT FROM myCur INTO @pkPerson

WHILE @@FETCH_STATUS = 0

BEGIN;

UPDATE dbo.tEmployee

SET ActiveRoles = dbo.udf_GetRoles(@pkPerson)

WHERE pkPerson = @pkPerson

FETCH NEXT FROM myCur INTO @pkPerson

END ;

CLOSE myCur ;

DEALLOCATE myCur ;

Luis Cazares SSC Guru Points: 183686 More actions · Answer 2

Agreed, you don't need a cursor but you might not even need a udf. If you share the code, we might be able to help you to speed this query. 😉

UDFs are terrible for performance.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

You are never blocked by yourself. Any locks that you hold are compatible with other locks that you request, they only prevent other people from taking incompatible locks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

info 58414 Hall of Fame Points: 3386 More actions · Answer 4

Hi,

You have understood the question. The cursor code runs with other code in a stored procedure. About a job with a dedicated SQL user.

Is it so that this user never can block yourself? This code runs during the night in any multi-user environment.

Best Regards and Thank You

Nicole

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Locks you take never prevent you from taking other locks. They prevent *other* sessions from taking incompatible locks.

Still doesn't explain why you have an unnecessary (slow, inefficient) cursor.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass