Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update stmt with in a cursor Expand / Collapse
Author
Message
Posted Wednesday, December 18, 2013 9:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 1:06 PM
Points: 55, Visits: 109
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
Post #1524214
Posted Wednesday, December 18, 2013 10:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1524216
Posted Wednesday, December 18, 2013 10:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 1:06 PM
Points: 55, Visits: 109
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)
Post #1524242
Posted Wednesday, December 18, 2013 10:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1524243
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse