Can it be done?

[t16turbo]

Hi,

Can Crystal reports be used to 'run' stored procedures?
I need to write these stored procedures that will populate 4 flat files for importing into another database.

These stored procedures will require parameters which woul dbe passed in using Crystal as a front end?
can this be done?

failing that, does anyone know another method by which I can acheive this?

thanks,

 

[mrees]

t16turbo,

Yes this can be done as you can base a CR on a SQL SP. We use a similar method to update an audit table that records sp run time and detail parameters that have been chosen e.g

CREATE PROC dbo.MySP @MyEmployeeNo INT
AS
DECLARE
 @Ident AS INT
--Update audit
INSERT INTO dbo.Audit(StartTime) VALUES(getdate())
SET @Ident = @@Identity --Bad form I know...

--Return Employee Records
SELECT
   field1
  ,field2
  ,field3
  .
  .
  ,fieldn
FROM
  EmployeeData
WHERE
  EmployeeNo = @MyEmployeeNo

--Close Audit Record
UPDATE dbo.Audit
SET
  @EndTime = getdate()
WHERE
  AuditID = @Ident

--Insert required parameter into another table
INSERT INTO dbo.ParamsPassed(Param) VALUE @MyEmployeeNo

I know this example is full of holes etc - but it shows that Crystal can run an SP that does other the return data and can use the parametet passed to it via Crystal itself.

I'm personally dead against using Crystal SP to do anything other than return data (and maybe record run times). You would be better off designing some ASP pages that incorporate a Crystal Viewer.

mrees

 

[synapsevampire]

Why not just let the database do it all and keep Crystal out of the equation?

CHeck with your dba, it should be easy for them to set up.

-k

 

[t16turbo]

There needs to be user interaction each time this is run, by one of the finance staff.
They will enter the parameters in a front end.

I think i'll just get one of the VBA guys to develop a simple app. to fire off the parameters to the database.