FreeTDS Development Group

["Polyanovskyi, Sasha" <sasha.polyanovskyi AT oa.com.au>]

> The difference between the procedures is the order of the first two 
> statements in the procedure body
> _________________________________________
> 
> CREATE PROCEDURE myProc
> (
>       @total int OUTPUT
> )
> AS
> BEGIN
>      select * from indial
> 
>      SELECT @total = COUNT(*) from service
> 
>      declare @sum int
>      set @sum = @total + 5
>      return @sum
> END
> 
> _________________________________________
> 
> 
> CREATE PROCEDURE myProc
> (
>       @total int OUTPUT
> )
> AS
> BEGIN
>      SELECT @total = COUNT(*) from service
> 
>      select * from indial
> 
>      declare @sum int
>      set @sum = @total + 5
>      return @sum
> END


I've changed the code to the suggested way, now neither case  :-)
(actualy my procedures are a bit different in a number of params that get
passed)

Suggested way:
        while (dbresults() != NO_MORE_ROWS)
        {
                process columns...;
                process return status...;
                process results;
        }

HERE IS OUTPUT FOR CASE 1:
### Call to dbresults: 1 ###
############################
Number of columns: 7
primary key in row 1: 99787050
primary key in row 2: default
Number of return values: 0
############################
        where are the return status and the return values???


HERE IS OUTPUT FOR CASE 2:
### Call to dbresults: 1 ###
############################
Number of columns: 0
Number of return values: 0
############################

### Call to dbresults: 2 ###
############################
Number of columns: 7
primary key in row 1: 99787050
primary key in row 2: default
Number of return values: 0
############################
        what was the first call doing?,
        where are the return status and the return values???


NOW, if I do something like this :-)
        while (dbresults() != NO_MORE_ROWS)
        {
                process columns...
                dbresults();    /* !!!!!!!!!!!!!!!!! */
                process return status
                process results
        }


For the procedure type 2, it crashes (core dump). That's OK :-)
But for the procedure type 1, I get the following:

### Call to dbresults: 1 ###
############################
Number of columns: 7
primary key in row 1: 99787050
primary key in row 2: default
Number of return values: 3
Output param 1: name=@total, data=2, length=4
Output param 2: name=@total3, data=0, length=4
Output param 3: name=@total2, data=1, length=4
############################
        where is the return status?
        and look what I get in the endless lop from then on!!!

### Call to dbresults: 2 ###
############################
Number of columns: 7

Return status is 3

Number of return values: 3
Output param 1: name=@total, data=2, length=4
Output param 2: name=@total3, data=0, length=4
Output param 3: name=@total2, data=1, length=4
############################

### Call to dbresults: 3 ###
############################
Number of columns: 7

Return status is 3

Number of return values: 3
Output param 1: name=@total, data=2, length=4
Output param 2: name=@total3, data=0, length=4
Output param 3: name=@total2, data=1, length=4
############################

etc.............................



Here is what works for case 1:
        dbresults();            /* call 1 */
        process columns...
        dbresults();            /* call 2 */
        process results...
        process return status...

Here is what works for case 2:
        dbresults();            /* call 1 */
        dbresults();            /* call 2 */
        process columns...
        dbresults();    
        process results...      /* call 3 */
        process return status...

Here is what I mean by "works":
=======================
Number of columns: 7
 Primary key in row 1: 99787050
 Primary key in row 2: default
Number of return values: 3
 Output param 1: name=@total, data=2, length=4
 Output param 2: name=@total3, data=0, length=4
 Output param 3: name=@total2, data=1, length=4
Return status is 3


***************************************

I do not know when I get to trying to solve this problem myself (I am at
work and I have lots of other stuff to do), but I'll try :-)