stored procedure/trigger error

[Former Member]

Using ASA 10 on a windows windows server 2003. I have a stored procedure defined with 'No Result Set' defined. Table a has a trigger that inserts into table B. Table B has a trigger that at the end we have added a call to the new stored procedure. I am getting an error message that says 'Result set not permitted in 'sp_insert_rpt_q_generic' (the new procedure). Not sure what is causing this. Tried debugging but get error message before hitting first line of sp.

example of proc call:

call dba.sp_insert_rpt_q_generic(
@ls_reference,0,null,null,new_name.incident_id,null,current user,null,null)

example of proc dec:

create PROCEDURE "DBA"."sp_insert_rpt_q_generic"(
@invrefnum varchar(10),@rpt_id integer,@a_pts char(200),@dw_report char(75),@a_inc 
char(20),@statusid integer,@a_who char(15),@objid integer,@SS_snd integer)
NO RESULT SET
begin

I do select information into variables in the proc, but none are returned only inserted into a table (if I ever get that far).

We did try recreating this as a function but same issue.

Any input or ideas would be appreciated.

[former_SQLA_member1694868]

Is the last statement before the end statement of the procedure some sort of select or return ?

Does the insert into the temporary table cause any triggers to fire that might return something?

[VolkerBarth]

"if I ever get that far" - MESSAGE statements inside the STP (in combination with dbsrv10 -o Log.txt) are fine to find out how far you are going - as is the stored procedure debugger....

[Former Member]

Well I was using the debugger, that was what was frustrating me. After reading the replies realized that the debugger was set to debug a specific user id. Reset debugger to all users and was able to find the issue (a malformed sql statement). Once corrected all went well.

Thanks again for the input

[Breck_Carter]

That's just nasty: "set to debug a specific user id"... I could see myself struggling for HOURS before realizing that 🙂