Re: [sqr-users] No Value returned from Sybase Stored procedure

["Ian Mills" <Ian.Mills@orix.com.au>]

Rick,

Thanks for your assistance. I tried to do as you suggested and got the
following error

(SQR 5528) Sybase CT_RESULTS error in cursor 10:
   (201) Procedure osp_sec_calcschedule expects parameter
@schedule_out, which was not supplied.

So I added "@schedule_out = $ls_temp" and got the following error

(SQR 5508) The number of EXECUTE...INTO &columns does not match the
procedure.

If I take away the INTO part the report doesn't crash but the stored
procedure still returns no data.


Then I changed my thinking. I knew the stored procedure was working,
just not with the execute command. I Created a single column temporary
table to store the value. Then ran the stored procedure within a
BEGIN-SQL block and inserted the returned value into the temporary
table. Then used a BEGIN-SELECT block to get the value from the stored
procedure. Below is the final procedure that now works.


begin-procedure get_schedule($as_contract_number)
    declare-variable
        text    $as_contract_number
    end-declare

    begin-sql
    delete from [$_gs_table_name]
    DECLARE @structure varchar(30)
    exec osp_sec_calcschedule $as_contract_number, @structure out
    insert INTO [$_gs_table_name] values(@structure)
    end-sql

    begin-select
structure       &structure=char
    let $_gs_schedule = &structure
from [$_gs_table_name]
    end-select

end-procedure


Hopefully others can use this if they ever encounter the same problem.

Regards,
Ian Mills
Systems Analyst
ORIX Australia Corp Ltd


>>> Spoth.Richard@healthnow.org 07/01/2004 10:49:54 pm >>>
I forgot to mention you also need to define the data type for
&schedule. 
It should be the same as the value in the SELECT statement of the SP.

 execute osp_sec_calcschedule @INPUT_VARIABLE = $as_contract_number
        INTO &schedule  char(8)


Rick Spoth





"Richard C Spoth" <Spoth.Richard@healthnow.org>
Sent by: sqr-users-admin@sqrug.org 
01/07/04 06:41 AM
Please respond to sqr-users

 
        To:     sqr-users@sqrug.org 
        cc: 
        Subject:        Re: [sqr-users] No Value returned from Sybase
Stored procedure


Ian, 

Try this.  Where @INPUT_VARIALBE is verbatim to the variable in the
SP.

 execute osp_sec_calcschedule @INPUT_VARIABLE = $as_contract_number
        INTO &schedule

 Let $_gs_schedule = &schedule
    #debugt show ':' $_gs_schedule ':'

Rick Spoth





"Ian Mills" <Ian.Mills@orix.com.au>
Sent by: sqr-users-admin@sqrug.org 
01/06/04 10:12 PM
Please respond to sqr-users

 
        To:     <sqr-users@sqrug.org>
        cc: 
        Subject:        [sqr-users] No Value returned from Sybase
Stored 
procedure


Hi all,

I was wondering if anyone would be able to help me with this strange
problem. I am trying to use a stored procedure running against a
Sybase
database and the output variable doesn't contain any value. Source
code
below. I have tested the stored procedure in a query analyser tool and
it works okay.

begin-procedure get_schedule($as_contract_number)
    declare-variable
        text    $as_contract_number
    end-declare
    execute osp_sec_calcschedule $as_contract_number, $_gs_schedule
out
    #debugt show ':' $_gs_schedule ':' 
end-procedure


I use similar functionality in 2 other reports and the values are
returned correctly. (shown below)
execute osp_arrears_calcfields $as_lease $as_port $as_branch $as_prod
$as_sub #ai_del1 out #ai_del2 out #ai_del3 out #ai_del4 out #ai_del5
out
#af_arrears out


I am running SQR v6.1.3

Any assistance will be appreciated.

Regards,
Ian Mills
Systems Analyst
ORIX Australia Corp Ltd

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 





CONFIDENTIALITY NOTICE: This email message and any attachments are for
the 
sole use of the intended recipient(s) and may contain proprietary, 
confidential, trade secret or privileged information.  Any unauthorized

review, use, disclosure or distribution is prohibited and may be a 
violation of law.  If you are not the intended recipient or a person 
responsible for delivering this message to an intended recipient,
please 
contact the sender by reply email and destroy all copies of the
original
message.

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users