troubleshooting Question

Using TSQL to update and interst based upon a select query

Avatar of m3housing
m3housing asked on
Microsoft SQL ServerSQL
12 Comments1 Solution814 ViewsLast Modified:
Hi Im new TSQL and after googling to try and find what im looking for I just got swamped in results.
Im a VBA programmer but I thought Id give it a stab doing it into TSQL. What im trying to do is open a recordset, then loop through the record set and update and amend the current record row based upon a certain condition. The code iv got doesnt work, can anyone point me in the right direction?

DECLARE @Schemeid double
DECLARE @Drawdown Int

DECLARE my_Cursor Cursor for
--Scheme id is the primary key
select Schemeid from scheme inner join Status on scheme.status = status.statusid
Where status.allowAppraisaledit = 0

Open my_Cursor

--loop and insert the next schemeid into the variable @schemeid
Fetch Next from My_Cursor
Into @Schemeid

WHILE @@Fetch_Status = 0
Begin
--Select the value drawdown  from the table scheme based upon the primary key
@Drawdown =  Select drawdown from [scheme] where schemeid  = @Schemeid
      -- if the value is above zero then amend the value and put a nice little note to inform the user
      If @drawdown > 0 then
            Update [scheme] set drawdown = 0 where schemeid = @schemeid
            
      Insert into schemenote (Schemeid, NoteOrder,Note, Alert)
     Values(@schemeid,0,'Previous Mortgage drawdown at ' & @Drawdown & 'Weeks',0)
            
      End      

End

Close My_Cursor
Deallocate My_Cursor
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros