Bug? where is the second empty result set

Bug? where is the second empty result set

Post by Michae » Thu, 22 Nov 2001 09:11:17



hi, all.

On SQLserver7 EE&sp2 , on windowNT4.0EE&sp6

In a stored procedure, I have three result sets return.  Right after the
first resultset, I have a check like  if there is no rows affected in the
first result set, i raise an error.  And, I suppose the following two result
will return, even they maybe empty.

My problem is, if the error raised and the second result set is empty, it
DOESNOT return.

In the following example,  if I execute
exec t_sp 1
everything is ok, please note the second result is empty.

But, if I run
exec t_sp 4

this will return.

rowid       rowname
----------- ----------

Server: Msg 50000, Level 16, State 1, Procedure t_sp, Line 10
Error
rowid       rowname
----------- ----------
1           a
2           b
3           c

Where is the second empty result set ???  Bug?

Thanks in advance.

-- scripts
create table t_table (rowid int, rowname char(10))
insert into t_table values (1,'a')
insert into t_table values (2,'b')
insert into t_table values (3,'c')
go

create procedure t_sp

as
 set nocount on

 -- result set 1


 begin
  raiserror ('Error', 16, 1) with seterror
 end

 -- result set 2
 select * from t_table where rowid = 0

 -- result set 3
 select * from t_table
go

--
-
Best Regards

              /'"`\  zzzZ  |
             ( - - )          |
--oooO--(_)--Oooo-----------------------------------------------------
Life Is A Journey,  Enjoy The Ride.
**********************************************************************
**********************************************************************

 
 
 

Bug? where is the second empty result set

Post by Greg Obleshchu » Thu, 22 Nov 2001 11:36:56


Micheal,
    It has to do with you raiseerror statement.  When you raise an error

number.  This is then effecting the next statement.
If you change the raiseerror to this
raiserror ('Error', 9, 1) with seterror then things will work for you

regards
greg O


> hi, all.

> On SQLserver7 EE&sp2 , on windowNT4.0EE&sp6

> In a stored procedure, I have three result sets return.  Right after the
> first resultset, I have a check like  if there is no rows affected in the
> first result set, i raise an error.  And, I suppose the following two
result
> will return, even they maybe empty.

> My problem is, if the error raised and the second result set is empty, it
> DOESNOT return.

> In the following example,  if I execute
> exec t_sp 1
> everything is ok, please note the second result is empty.

> But, if I run
> exec t_sp 4

> this will return.

> rowid       rowname
> ----------- ----------

> Server: Msg 50000, Level 16, State 1, Procedure t_sp, Line 10
> Error
> rowid       rowname
> ----------- ----------
> 1           a
> 2           b
> 3           c

> Where is the second empty result set ???  Bug?

> Thanks in advance.

> -- scripts
> create table t_table (rowid int, rowname char(10))
> insert into t_table values (1,'a')
> insert into t_table values (2,'b')
> insert into t_table values (3,'c')
> go

> create procedure t_sp

> as
>  set nocount on

>  -- result set 1


>  begin
>   raiserror ('Error', 16, 1) with seterror
>  end

>  -- result set 2
>  select * from t_table where rowid = 0

>  -- result set 3
>  select * from t_table
> go

> --
> -
> Best Regards

>               /'"`\  zzzZ  |
>              ( - - )          |
> --oooO--(_)--Oooo-----------------------------------------------------
> Life Is A Journey,  Enjoy The Ride.
> **********************************************************************
> **********************************************************************


 
 
 

Bug? where is the second empty result set

Post by Michae » Fri, 23 Nov 2001 01:51:49


Thanks for your reply.

The interesting thing is that if the second result set is not empty,  after
the error,  it will be shown up correctly.

thanks.


> Micheal,
>     It has to do with you raiseerror statement.  When you raise an error

> number.  This is then effecting the next statement.
> If you change the raiseerror to this
> raiserror ('Error', 9, 1) with seterror then things will work for you

> regards
> greg O

 
 
 

1. make cursor out of second result set and discard the rest

When processing the results of some stored procedures, the
desired results may be in the second or third result set.  
I would like to be able to turn one or all of the result
sets into cursors within a stored procedure of my own
creation.  The cursor syntax seems to want a select
statement, so I can insert data from the first returned
result set into a temporary table and then turn a select
statement on the temporary table into a cursor.  This
method only allows data from the first returned result set
to be used.  

A related problem is discarding the data sets that are not
needed so the user only receives one final result.  I
believe this problem can be solved by putting all the
possible result sets into cursors that may or may not be
used, but a more elegant solution is desired.

2. Can anyone help?

3. result set empty(but thers data)

4. Hot failover.

5. unixODBC - Inline TDS Driver 1.6 - Data in result set is empty

6. SQL Server Licences

7. HELP Needed - Heavy Load causing Sparatic Empty Result Sets (SQL 7)

8. Change BDE alias in run-time ?

9. Trapping empty result set

10. Join with empty result set, again...

11. empty result set even though there is data

12. Trapping empty result set

13. Can't process multiple result sets when first resultset is empty