Returning Values From Stored Procedures

  • 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?

  • 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.


  • Why not output variables???

  • They say that lazy people take most pains!

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

    INSERT Tbl_X


    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 3 (of 3 total)

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