'Calling' a stored procedure

[MSBrady]

Oy!
CR XI
SQL Server 2000
OLE DB (ADO) Connection

I have a patient statement report that is using a couple views as it's data source. These statements will be run once a month or more. At runtime I need to be able to have Crystal execute an SQL Expression from w/in the report that in turn executes a stored procedure on the db.
I have seen many posts regarding using SP's as the data source, but none on 'calling' the SP from Crystal. Is this possible?

Currently I have tried creating an SQL Expression Field with simply the name of the sproc, this returns an error 'Invalid column name'. I tried the full syntax
(use db
go
exec sproc)
which returns an error 'Incorrect syntax near the keyword 'use''. I tried just
(exec sproc)
which returns an error 'Incorrect syntax near the keyword 'exec''

 

[rosemaryl]

Dear MSBrady,

I was easily able to do this in 8.5, but in 10 I can't.

However, executing a udf still works and a udf can call a stored proced (you have to be sneaky sometimes).

Make sure the udf returns just one value .. maybe a 1 to show it executed.

You call a udf like this:

(Owner.UserDefinedFunction(P1,P2...))

Where p1, p2 ... is equal to the parameters required by the udf.

best regards,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[MSBrady]

Hello RO,

The requirements have shifted slightly since earlier this morning. If I take your approach can the UDF pass the SP parameters at runtime from the report? I haven't ever written one so I'm not familiar with them.

In other words, if my SP is expecting 4 parms can the UDF read report fields and pass them to the SP? This is all so we can track out going patient statements for historical lookup purposes. So the SP will take those parms which are 4 fields from the report and then insert into another table which then can be used to assign invoice numbers to.

 

[MSBrady]

Just wanted to finalize this issue:
Appearantly a UDF can't call a sproc that modifies data? This is what the .Net programmer is telling me. We have decided to write a .Net application that handles the sprocs and data formatting then passes the final dataset to my report.

thanks for the replies.

.bat man