sybase - procedure returns previous result set

Question

I have a procedure with 2 selects, looks something like this...

PROCEDURE getRec
  @pId INTEGER
AS
BEGIN
  SELECT 'anything'

  SELECT id, name
  FROM my_table
  WHERE id = @pId
END

When my perl script calls this stored procedure, if my_table has a matching record then it is displayed. However, if the ID passed in has no matches then the stored procedure returns 'anything'.

If there are no rows in the second select then I just want the procedure to return an empty result set. How can I achieve this?

Answer 1

Every SELECT will produce a result set (with the exception of SELECT @var = ...)

So will first be receiving the 'anything' result set

After that, you will receive the empty result set.

You need to get your perl code to fetch all rows in the first result set, then get the next result set and fetch all rows in that result set.

The functions to get the next result set will greatly depend on the perl library you are using.