How run stored procedure in Crystal report?

[Former Member]

I have table from query in report, but first I need to run stored procedure in report.

How I need to do it?

[Former Member · Accepted solution]

Hi,

go to database expert

select your connection and click on add command their you will have to create parameters if required and pass it to a proc

for ref..

{CALL `menagerie`.`sp_timer`({?delay time})}

where `menagerie`.`sp_timer` is a stored proc.

and delay time and is a parameter.

[Former Member]

Hi

When you create a report based on a stored procedure and click on refresh, the stored procedure is run and get the values from database.

Would appreciate if you could explain in details about the exact requirement.

Regards

Sourashree

[Former Member]

When I need to run my report

1) I need to run storeed procedure ( the stored procedure will update some tables)

I just used Add Command and added stored procedure in Database Fields, but I think the procedure doesn't work in report. Maybe I need add new commant and write there:

exec ProcedureName {?parameter1} {?parameter2}

What I need to do?

2) I use some query in report

ex.

select t1.col1, t2.col2, t6.col3 t1.col7 t4.col1

from t1, t2, t6, t4, r4, ju, hh

where hh.col11={?parameter1}

and ju.col3=6

and r4.col3={?parameter2}

group by .....

order by ......

How I need to create me report? What I need to do first?

[Former Member]

I have Oracle database

Edited by: Inn Kont on Feb 27, 2009 6:07 PM

[Former Member]

previous post not correct

-

---

this is correct

When I need to run my report

1) I need to run storeed procedure ( the stored procedure will update some tables)

I just used Add Command and added stored procedure in Database Fields, but I think the procedure doesn't work in report. Maybe I need add new commant and write there:

exec ProcedureName par1 par2
or
exec ProcedureName (par1={?parameter1} par2={?parameter2})

It's doesn't work. What I need to do?

2) I use some query in report

ex.

select t1.col1, t2.col2, t6.col3 t1.col7 t4.col1
from t1, t2, t6, t4, r4, ju, hh
where hh.col11={?parameter1}
and ju.col3=6
and r4.col3={?parameter2}
group by .....
order by ......

How I need to create me report? What I need to do first?

If parameter in stored procedure par1={?parameter1} in query

and par2={?parameter2}

[Former Member]

I would put both the EXEC of the Stored Procedure and the select statement in the SAME "Add Command" dialog, so the stored procedure runs first, then the select.

HTH,

Carl