Update stmt with in a cursor

  • have a requirement that to update data from last year to current year.(means like last year what is the data eligible this year also should eligible) the below are two querys (I need to do innerjoin for second query and I need to put in cursor and top query need to update it like every time loop runs and we can use ASC systemid in update and need to set three colums in SNLunchSevereNeeds table in the update statement where HHFKoverride = 1 and HHFkprocess =1 and payfrom =7

    Basically needs to update from last year to current year

    Created a SP,not sure its right or not?can anyone help me!!1

    CREATE PROCEDURE [dbo].[usp_SNSixcentsEligibilityList]

    @SystemId Varchar(10),@FiscalYear int

    AS

    BEGIN

    declare @SystemCertificationID int

    SET NOCOUNT ON;

    declare EligibleList cursor for

    Select * from SNLunchSevereNeeds where SystemCertificationID in(select SC.SystemCertificationID

    from SNSystemCertification SC INNER JOIN SNLunchSevereNeeds LS ON SC.SystemCertificationID = LS.SystemCertificationID

    where FiscalYear=@FiscalYear and LS.IsActive = 1)

    open EligibleList

    FETCH NEXT FROM EligibleList INTO @SystemCertificationID,@SystemId,@Fiscalyear

    while @@FETCH_STATUS=0

    begin

    Select * from SNLunchSevereNeeds Where SystemCertificationID in(

    Select SystemCertificationID from SNSystemCertification where FiscalYear=@FiscalYear and

    IsActive=1 order by SystemID ASC )

    --write an update to set the values.

    update SNLunchSevereNeeds

    set HHFKOverride =1,

    HHFKProcess = 1,

    HHFKPayFrom = 7

    FROM SNLunchSevereNeeds where IsActive=1

    FETCH NEXT FROM EligibleList INTO @SystemCertificationID,@SystemId,@Fiscalyear

    End

    Close EligibleList

    deallocate EligibleList

    END

    GO

  • Hi and welcome to the forums. Why do you need a cursor for this? From what you posted I think you can replace the entire body of your stored proc with this.

    update SNLunchSevereNeeds

    set HHFKOverride = 1,

    HHFKProcess = 1,

    HHFKPayFrom = 7

    from SNLunchSevereNeeds

    where SystemCertificationID in

    (

    select SC.SystemCertificationID

    from SNSystemCertification SC

    INNER JOIN SNLunchSevereNeeds LS ON SC.SystemCertificationID = LS.SystemCertificationID

    where FiscalYear = @FiscalYear

    and LS.IsActive = 1

    )

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Using these two querys

    Select * from SNLunchSevereNeeds Where SystemCertificationID in(

    Select SystemCertificationID from SNSystemCertification where FiscalYear=2014 and

    SystemID=749 And IsActive=1 )

    Select * from SNLunchSevereNeeds Where SystemCertificationID in(

    Select SystemCertificationID from SNSystemCertification where FiscalYear=2013

    And IsActive=1)

    How can I update data from last year to current year (need to check system certification id from both the tables)

    see the current fiscal year for all the systems who are eligible.(updating from last year to current year)

  • mcfarlandparkway (12/18/2013)


    Using these two querys

    Select * from SNLunchSevereNeeds Where SystemCertificationID in(

    Select SystemCertificationID from SNSystemCertification where FiscalYear=2014 and

    SystemID=749 And IsActive=1 )

    Select * from SNLunchSevereNeeds Where SystemCertificationID in(

    Select SystemCertificationID from SNSystemCertification where FiscalYear=2013

    And IsActive=1)

    How can I update data from last year to current year (need to check system certification id from both the tables)

    see the current fiscal year for all the systems who are eligible.(updating from last year to current year)

    You have me at an extreme disadvantage here. I have no idea what your tables and data are like. Take a look at the first link in my signature about best practices when posting questions. With some details about your data and what you want to do it we can help you figure out how to do it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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