Start reading table at specific point? 1

[Wyldcard9]

In Cobol, and Magec I can Start a read on a file at a specific point, and read to a specific point. Is there a comparable way to do that in SQL? I can also kick out of the read loop if a record is found(and I know I only want to find one).

Sample data from table:
SECTION ENTRY VAL_STR
--------------------------------------------------------
CONTRACTS 36 IN NEGOTIATIONS
DTYPE 73 Unfreeze Bank
RECIPAUTHORIZATION 15001A FUND 15 CODE 001 ANNUITY
RECIPAUTHORIZATION 99999P FUND 99 CODE 999 PENSION

There are thousands of records between these samples for the RECIPAUTHORIZATION. I have Section, and Entry, and read the table to get Val_str, and write it back to a table that is then exported to a file loaded to the Mainframe. What I would like to do is start my reading of the table at the exact point I should find a record, and then when I find a record kick out of the loop. So, I guess I am asking two things:

1. How to start
2. How to kick out when a record found, since I know I can only find one with that key.

This is my current code:
DECLARE @doctype varchar(10)
DECLARE @doc_DESC varchar(50)
DECLARE @val_str varchar(255)
DECLARE @entry varchar(30)

DECLARE the_cursor CURSOR FOR
SELECT val_str, entry FROM [Kovis_Framework].[DBO].[LKU]
WHERE SECTION = 'RECIPAUTHORIZATION'

OPEN the_cursor
FETCH NEXT FROM the_cursor into @val_str, @entry

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM]
SET Annuity_doc_descr = @val_str
WHERE Annuity_doc_type = @entry

UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM]
SET Pension_doc_descr = @val_str
WHERE Pension_doc_type = @entry

UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM]
SET Health_doc_descr = @val_str
WHERE Health_doc_type = @entry

FETCH NEXT FROM the_cursor into @val_str, @entry
END
CLOSE the_cursor
DEALLOCATE the_cursor
GO

This along with one other job takes 2-3 minutes to run. I know I am only going to get more RECIPAUTHORIZATION records, and want to redesign this before it becomes a problem with running too long. All my other SQL jobs take under 10 seconds to run.

 

[philhege]

I think you can re-write this SP as a bulk update statement or three. At the very least, you can get rid of the cursor.

UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM] a
SET a.Annuity_doc_descr = b.val_str
FROM Kovis_framework.dbo.LKU b
WHERE a.Annuity_doc_type = b.entry
AND b.section = 'RECIPAUTHORIZATION'


UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM] a
SET a.pension_doc_descr = b.val_str
FROM Kovis_framework.dbo.LKU b
WHERE a.pension_doc_type = b.entry
AND b.section = 'RECIPAUTHORIZATION'

UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM] a
SET a.health_doc_descr = b.val_str
FROM Kovis_framework.dbo.LKU b
WHERE a.health_doc_type = b.entry
AND b.section = 'RECIPAUTHORIZATION'

CAVEAT EMPTOR: Please test this code in a development environment.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[Wyldcard9]

philhege,
Okay thanks. I will give that a try

 

[Wyldcard9]

philege,

I took out the a, and it worked great. For some reason it would not compile with it.

UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM]
SET Annuity_doc_descr = b.val_str
FROM Kovis_framework.dbo.LKU b
WHERE Annuity_doc_type = b.entry
AND b.section = 'RECIPAUTHORIZATION'


UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM]
SET pension_doc_descr = b.val_str
FROM Kovis_framework.dbo.LKU b
WHERE pension_doc_type = b.entry
AND b.section = 'RECIPAUTHORIZATION'

UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM]
SET health_doc_descr = b.val_str
FROM Kovis_framework.dbo.LKU b
WHERE health_doc_type = b.entry
AND b.section = 'RECIPAUTHORIZATION'

Thanks again.

 

[philhege]

Right, my bad; you can't use an alias in this situation.

Glad to be of help.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?