my select statement return miltuple rows.
That is correct, documented behaviour. If you load @variables via a select which returns multiple rows, then (obviously) only the values from one row can be loaded into the @vars; ASE loads the last row. What is your intent here (forget sp_A vs sp_B for a moment):
but my code sent the las row to sp_B proc.
• to select one row, or multiple rows ?
• to insert multiple rows in table_B from table_A ?
How should I chance my code to procedure all rows that my select statement returns.
(There is something fundamentally wrong, when you select from one table, and insert into another table; fixing that problem will eliminate the proc altogether; however that is a separate problem; I will answer your question.)First, you need to understand that you trying (thinking, designing you code) to perform row-processing operations, one-row-at-a-time mentality. The server is a relational set-oriented engine, you need to think in terms of relational sets. Sybase processes set very efficiently; row-processing is very slow, and you need to implement a cursor.
1 Set Processing
Try this, sp_B is not required. Make sure the transaction size is small:
2 Row Processing
INSERT table_B <column_list>
The assumption is that sp_B is a transactionally secure proc for inserting rows into table_B, one row at a time, and must be used (that is a Good Thing). You need a cursor.
Cursors are slow and use an additional class of locks; there are many problems, eg: isolation levels; sensitivity to changes in underlying tables. Therefore I avoid them entirely. Here is a faster method, which uses non-cursor locks, has no problems re isolation and sensitivity, but of course uses cursor structure.
Don't forget you error checking.
SET ROWCOUNT 1
SELECT @column1 = column1, -- load vars for one row
@column2 = column2,
@column3 = column3,
@column4 = column4
IF @@ROWCOUNT != 1 -- no more source rows
EXEC @ReturnStat = sp_B
IF @ReturnStat != 0
PRINT "Error ..." -- or RAISERROR
SET ROWCOUNT 0