Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update stmt with in a cursor


Update stmt with in a cursor

Author
Message
mcfarlandparkway
mcfarlandparkway
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 459
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
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)
mcfarlandparkway
mcfarlandparkway
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 459
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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search