How to set while statement equal to the end of the record file

[maxamis4]

I am migrating from MS Access to MS Sql. I know access uses the eof which means end of record file. How do i create a while statement that I can set equal to the end of the record file.

I have a list of records 30,000 and I need to loop through them one by one.

[ck9663]

Although it is not advisable and I don't know why you need it to read one record at a time, but if that's your requirement, you're going to need to use CURSOR.

There might also be a set-based way of doing it. Post what you need to do or your sample code so that we can give a more efficient suggestion.

Good Luck!!!

~~ CK

[maxamis4]

MS SQL requirements

Thanks for your response My requirements are as follows:

The purpose of my stored procedure is to change records in a resolved status to closed. By making this change I will have to update multiple tables that are impacted by the change.


My thinking behind it was to identify all the records that are in a resolved status. From there get the unique id that is used by the other two child tables and update/insert them based on that unique id.

Based on this I was thinking of using a while statement as I cycle through the records to update the two child tables and the main table.

My stored procedure is pretty general because I have just started coding and I have only declared my variables.

I was thinking of using my counter on my while statement as the total number of records that returned from the parent table and the total number of tickets that were resolved.

SET @COUNTER = (SELECT COUNT(dbo.call_req.status) AS Stat_Count FROM bo.call_req INNER JOIN dbo.ca_contact ON dbo.call_req.group_id = dbo.ca_contact.contact_uuid WHERE(SUBSTRING(dbo.ca_contact.last_name, 1, 3) = 'GSA') AND (dbo.call_req.status = N're'))

.

As I mentioned I have a strong MS Access background but if there is a better method of doing it I welcome it. If you have links or examples that would be very helpful.

thanks for the help

[ck9663]

Here are some steps you could follow.

1. Enclosed the entire process in TRANSACTION. This should help you rollback the data just in case something happen.
2. Use TRY..CATCH. Place the rollback in CATCH part.
3. Use the OUTPUT CLAUSE. To identify what records were updated. Something like this:

UPDATE YOURTABLE 
set STATUS = 'CLOSED' 
OUTPUT INTO #temptable
FROM YOURTABLE
where STATUS = 'RESOLVED'

Your #temptable should have the same structure as the table you're updating.

4. Update all the other table using the #temptable. Something like this:

Update YourOtherTable
set whateverfields = towhatevervalue
from YourOtherTable
inner join #temptable t on YourOtherTable.UniqueId = t.UniqueId

5. Commit your transaction


Happy Coding!!!

~~ CK