Tom Moreau
3/30/2007 3:23:00 PM
Change the last lines to:
FETCH NEXT FROM cPlansBen INTO
@PlanType,
@PlanKey
END
i.e. put the END statement at the, ahem, end.
That said, you really have to look at rewriting this to shuck the cursor.
If you give us a brief business spec, I imagine this whole thing can be
reduced to just a few lines of code.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
<danielle.m.manning@gmail.com> wrote in message
news:1175267717.188673.289410@p15g2000hsd.googlegroups.com...
On Mar 30, 11:07 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote:
> There's nothing wrong with your SELECT. How about showing us the cursor
> code, including the entire loop?
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .<danielle.m.mann...@gmail.com> wrote in message
>
> news:1175266498.801197.10310@n59g2000hsh.googlegroups.com...
> Hi all, I am hoping you can help me. I have a sql statement which
> returns three rows, which i am trying to use a cursor to loop through
> (yuck, i know! But this is the existing paradigm of the code I am in,
> and I don't want to change it drastically).
>
> The select statement I am working with currently returns three rows:
>
> SELECT [dbo].[fn_GetPlanTypeName](PlanTypeKey) AS PlanType, [dbo].
> [fn_GetPlanKey_ByEnrollment] (EnrollmentKey) AS PlanKey
> FROM enrollments
> WHERE employeekey = @EmployeeKey
> AND [dbo].[fn_GetPlanTypeName](PlanTypeKey) IN ('Life And AD&D',
> 'Retirement', 'Voluntary Life', 'Voluntary AD&D')
>
> Results
> Plan Name Plan Key
> 1 Voluntary Life 877DC1DD-FCEA-4894-A4D8-EE13357C8B6E
> 2 Retirement 981CC0BB-4C05-40F8-B8A1-AED8CE0BE769
> 3 Life And AD&D 2C799F37-DDB5-43B6-B853-2CD54B72476E
>
> Unfortunately, my cursor can never move off of row 1. I am suspicious
> that there is an issue in my where clause. Does anyone have any
> suggestions?
>
> Thank you,
> Danielle
Ok, here is the whole block:
BEGIN
--Get all Plan Types, PlanKey they are enrolled in which might have
beneficiaries
DECLARE cPlansBen CURSOR FOR
SELECT [dbo].[fn_GetPlanTypeName](PlanTypeKey) AS PlanType, [dbo].
[fn_GetPlanKey_ByEnrollment] (EnrollmentKey) AS PlanKey
FROM enrollments
WHERE employeekey = @EmployeeKey
AND [dbo].[fn_GetPlanTypeName](PlanTypeKey) IN ('Life And AD&D',
'Retirement', 'Voluntary Life', 'Voluntary AD&D')
OPEN cPlansBen
FETCH NEXT FROM cPlansBen INTO
@PlanType,
@PlanKey
PRINT @PlanKey
PRINT @PlanType
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @ExpireDate = DATEADD(dd, 30, getdate())
SET @SignatureOnly = 1
IF @PlanType = 'Life And AD&D'
BEGIN
INSERT @TempStages
SELECT wizardstagekey, Stage, Step, @ExpireDate, @SignatureOnly
FROM wizardStages
WHERE StepName = 'Life And AD&D'
END
IF @PlanType = 'Retirement'
BEGIN
INSERT @TempStages
SELECT wizardstagekey, Stage, Step, @ExpireDate, @SignatureOnly
FROM wizardStages
WHERE StepName = 'Retirement'
END
IF @PlanType = 'Voluntary Life'
BEGIN
INSERT @TempStages
SELECT wizardstagekey, Stage, Step, @ExpireDate, @SignatureOnly
FROM wizardStages
WHERE StepName = 'Voluntary Life'
END
IF @PlanType = 'Voluntary AD&D'
BEGIN
INSERT @TempStages
SELECT wizardstagekey, Stage, Step, @ExpireDate, @SignatureOnly
FROM wizardStages
WHERE StepName = 'Voluntary AD&D'
END
IF @PlanType = 'Supplemental Group Life'
BEGIN
INSERT @TempStages
SELECT wizardstagekey, Stage, Step, @ExpireDate, @SignatureOnly
FROM wizardStages
WHERE StepName = 'Supplemental Group Life'
END
END
FETCH NEXT FROM cPlansBen INTO
@PlanType,
@PlanKey
For what it's worth - it never gets to the FETCH NEXT part. Ever. I
can't figure out why.
@TempStages is just a temporary table declared within the stored
procedure.