Returning Values From Stored Procedures

  • David.Poole

    SSC Guru

    Points: 75192

    I have a stored procedure that goes out and returns a record set.

    There are occassions when I would like to pass the values of the fields within the recordset into variables.

    I could do this using OUTPUT variables, but is there another way of doing this?

  • Andy Warren

    SSC Guru

    Points: 119676

    Not that I can think of. Typically you use output when you are working a singleton situtation. With a recordset by default you're saying Im not sure how many records I'll have, from 0 to gazillions. You're just trying to avoid the recordset overhead? You can sorta do this with ADO by pushing the recordset into an array. A little further out, you could return as XML maybe - but the overhead of opening a DOM is probably equal to a recordset.

    Andy

  • GRN

    Hall of Fame

    Points: 3210

    Why not output variables???

  • David.Poole

    SSC Guru

    Points: 75192

    They say that lazy people take most pains!

    I had seen a syntax for an INSERT statement that went something like:-

    INSERT Tbl_X

    EXECUTE Sp_Y

    Where Sp_Y returns a recordset.

    I was wondering if there were any other short cuts like that.

    I try to write my SPs so that they have multiple uses with the aim of reducing instances where I have a lot of very similar SPs.

    In this particular case my stored procedure has a number of optional arguments. If all are specified then a single record will be returned, otherwise multiple records will result.

    I'll chicken out and use OUPUT variables and put IF @@ROWCOUNT >1 or something similar.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply