Create Cursors using Dynamic Sql

Question

Post reply

Create Cursors using Dynamic Sql

prakash-310933

SSC Rookie

Points: 45
More actions
July 21, 2006 at 1:18 am

#113702

How are you and friends?
I have some doubt in SP. I want to create cursor dynamically using variable.
See SP below.
Declare @DyStr nvarchar(500)
IF @ProjLdrId = -111
        BEGIN
        Set @DyStr = Select A.PcbProjID_PK From PcbProjMstr A, PcbPinMstr B
                        Where A.PcbProjState Between 3 AND 5
        END
ELSE
        BEGIN
        Set @DyStr = Select A.PcbProjID_PK From PcbProjMstr A, PcbPinMstr B
                        Where A.PcbProjState =6
        END
        DECLARE ParentCur   CURSOR
        FOR @DyStr
Here Last line of the SP I am getting Error. How can i resolve this problem?
Please give the solution for this problem.. Urgent
Thanks
Prakash.r

Viewing 3 posts - 1 through 3 (of 3 total)

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

Sergiy SSC Guru Points: 110209 More actions · Answer 1

I would suggest do not create a cursor.

And having read some basics about SQL would be quite useful.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 2

Prakash,

Serqiy is, although a bit brusk, still absolutely correct. Why do you need a cursor at all? And building one dynamically is like the worst of two worlds. Why can't you just do this?

IF @ProjLdrId = -111

BEGIN

Select A.PcbProjID_PK From PcbProjMstr A, PcbPinMstr B

Where A.PcbProjState Between 3 AND 5

END

ELSE

BEGIN

Select A.PcbProjID_PK From PcbProjMstr A, PcbPinMstr B

Where A.PcbProjState =6

END

If we knew a bit more about what you were trying to do with the result set from above, perhaps we could help you avoid the overhead of a cursor.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)