How to Pass input Parameters to Stored procedure from JDE

Hi Lists,

I need to call a stored procedure(in sql server 2000) using the business function "Execute External Program". The stored procedure accepts tablename as input parameter and process the logic.
Can anyone guide me to call (syntax) the stored procedure from the business function "Execute External Program" and pass table name as input parameter to the stored procedure from JDE.

Thanks in advance

EONECNC

You could write an external program in one of the many programing languages (not sure what platform you're on or what your application is: a UBE or interactive program), but I have written business functions in the C programming language, but compiled with the sql includes to do native sql-c programming. This allows me to pass in native JDE parameters, which I can now "re-package" to make the sql calls. If your logic server is an Windows machine, you could program this quite easily. I wrote my bsfns in sql-c on an AS/400, which isn't too bad.

Good Luck

I'm writing a whole bunch of month end reports that would benefit if they were done in SQL proc(lots of left outer joins, complex inner joins - sub-strings etc) and I'm struggling how I would make the program visible to the users within E1 tools set.

Can you elaborate more on how you do this please? Are you creating a custom DLL in E1 using JDE standard API programing and then making connection to SQL server from your C program?

Wes

Just wanted to mention that frequently, this can be done the other way around: running the SQL report directly within the DB server with occasional calls to BSFN's. If this sounds like it can do the trick for you, then we do have a software solution for it:
ESI JDE Stored Procedure

Create a custom table with the SP to call along with the parameter data you want to pass in. Then create a table trigger on insert, parse the data, and exec the SP on the server.

We usually write a UBE for this since the UBE cannont finish until the trigger is done executing. So you know the SP is still processing since the write cannot commit with the trigger running. Once the trigger finishes the SP, the write commits and the UBE goes to a completed status.

Works very well for us.

Sweet. Thanks for the info.

Fantastic solution