Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Unanswered: call stored proc from multiple rows select statement

    Hi all,

    I have two stored proc. sp_A calls sp_B and sp_A is below.

    create procedure sp_A as
    BEGIN
    DECLARE
    @_column1 DATETIME,
    @_column2 VARCHAR(32),
    @_column3 VARCHAR(32),
    @_column4 VARCHAR(80)

    select @_column1 = column1,
    @_column2 = column2,
    @_column3 = column3,
    @_column4 = column4
    from tableA

    exec sp_B @_column1, @_column2, @_column3, @_column4
    END

    my select statement return miltuple rows. but my code sent the las row to sp_B proc. How should I chance my code to procedure all rows that my select statement returns.

    Regards.

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    my select statement return miltuple rows.
    Ok, fine.
    but my code sent the las row to sp_B proc.
    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):
    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:
    Code:
    INSERT table_B <column_list>
        SELECT <column_list>
            FROM table_A
            WHERE <conditions...>
    2 Row Processing
    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.
    Code:
    SET ROWCOUNT 1
    WHILE (1=1)
        BEGIN
        SELECT  @column1 = column1,  -- load vars for one row
                @column2 = column2,
                @column3 = column3,
                @column4 = column4
            FROM table_A
            WHERE <conditions...>
        IF @@ROWCOUNT != 1  -- no more source rows
            BREAK
        EXEC @ReturnStat = sp_B
            @column1,
            @column2,
            @column3,
            @column4
        IF @ReturnStat != 0
            PRINT "Error ..."  -- or RAISERROR
        END
    SET ROWCOUNT 0
    Don't forget you error checking.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •