Fetch Statement 1

[RSX02]

Hi there
I have a newbie question. I have this code in a stored procedure. Does somebody tell me what the fetch does? I know that it's a kind of array but...I don't know more than that. I need to do the same kind of thing with a table that it's called TblLot which has fields Item and Lot. It can have many lot for 1 item. So I guess that it's the good command to use for extract that and put it in the same variable right? Could you please help me with this?!
Thanks in advance


DECLARE SerialCrs CURSOR LOCAL STATIC FOR
SELECT
serial.RowPointer
, serial.ser_num
FROM serial
INNER JOIN ser_track
ON ser_track.ser_num = serial.ser_num
AND ser_track.track_num = @MatltrackTrackNum
WHERE serial.stat = 'O'
AND serial.item = @MatltrackItem
OPEN SerialCrs
WHILE @Severity = 0
BEGIN
FETCH SerialCrs INTO
@SerialRowPointer
, @SerialSerNum
IF @@FETCH_STATUS = -1
BREAK

 

[meydz]

FETCH means to get the next batch of record from your select statement.
if @@fetch_status = 0 there is more record if not an EOF has been reached.

hope this will help.
meydz

 

[RSX02]

Hi meydz
thank you for your answer. It help me to understand how the fetch works.
Another question.. This part of the code put each record in the variables that was call in my select right? How the variable is able to hold more than 1 value? This variable(@SerialSerNum)is declare as a nvarchar(30)?
And what about the severity field?

Thanks in advance once again!

WHILE @Severity = 0
BEGIN
FETCH SerialCrs INTO
@SerialRowPointer
, @SerialSerNum
IF @@FETCH_STATUS = -1
BREAK

 

[RSX02]

I create this code. Does one of you guys see any error(s) in that. Seems that it doesn't work. (my table name is Lot and my field is lot also)
thank U

DECLARE LotCrs CURSOR LOCAL STATIC FOR
SELECT
lot
FROM lot
Where item = @MatltrackItem
OPEN LotCrs
WHILE @Severity = 0
BEGIN
FETCH LotCrs INTO
@uflot
IF @@FETCH_STATUS = -1
BREAK

 

[SQLSister]

YOu are correct, the variables can hold only one value unless they are defined as table variables. Therefore the cursor replaces the value each time it selects a new record.

I presume that the value of @severity is set elsewhere inthe stored procedure.

Frankly I don;t see why anyone would use a cursor in this case. If you have many records it will run very slowly as cursors are inefficient. And if all you want is the last record, why go through all the others? Since you are just learning t-SQL I will tell you that you should really try to never use cursors. It is very rare that they need to used, generally they are necessary more for dealing with metadata than with user data becasue their real purpose is to allow you to run commands that only allow one input at a time such as pulling the tables names one at a time to add a last updated column to every table. Since alter table can only operate on one table at a time a cursor is a good choice to run through all the possible tables names to run the command. The real problem with cursors in user data is that the developers often deal only with small sets of test data and so use the inefficient methods because they find them easier to understand and then wonder why their application no longer works well when there are 2 million records.

 

[RSX02]

Hi SQLSister
I appreciate the time that you put is this thread.
The reason that I used a cursor is because the SP is already done. I have to modify it. And in the code that already exist have many cursor like that. So let me explain the situation here.
I have items in packing slip. Each Item is a line is my packing slip. For 1 line it could have more than 1 quantity ordered. So the lot works as a serial number. if it have 5 qty ordered for 1 item (ex: Cl-10000) it will have 5 lot as 5 serial number. So I have to put this 5 lot in a variable to put it in my crystal report.
The serial number is already done in my SP that it wasn't me who do that. I thought to do the same thing about the lot could be a good idea.
What do you think?
Thanks in advance

 

[RSX02]

I also have it. I had it with the serial number. So do you think I have to do that? If I don't put this code it gives me an error.

END
CLOSE LotCrs
DEALLOCATE LotCrs --END LOT

 

[meydz]

you have to put that code.
remember that it is a good practice to free the memory from unnecessary variables.
i think an error occurred because you missed to place END keyword

While
Begin
...
End
hope this will help
meydz

 

[RSX02]

This is my procedure. It's seems now to work. However I get the first Lot twice. For example if my lot has the value : 4,7,9 it will show: 4,7,9,4
Do you have an idea what could cause this?
Thanks

DECLARE LotCrs CURSOR LOCAL STATIC FOR
SELECT
lot
FROM lot
Where item = @ItemTemp
OPEN LotCrs
WHILE @Severity = 0
BEGIN
FETCH LotCrs INTO
@uflot
IF @@FETCH_STATUS = -1
BREAK
/*Then I do my insert into*/
END
CLOSE LotCrs
DEALLOCATE LotCrs --END LOT