Using Multiple Stored Procedures in Crystal Reports 1

[LeonelSanchezJr]

I have one report that uses Stored Procudure "FORM2". Stored Procedure "FORM2" calls Stored Procedure "FORM2A". When I run the stored procedure outside of Crystal, it works fine and returns two data sets (one from each stored procedure), however, when I preview my Crystal Report, only the record set from the first stored procedure is returned. What do I have to do in order to get the second recordsset.

I will have a total of 8 stored procedures, so I have to do it this way.

 

[balves]

I believe that Crystal can only report off one recordset from a stored procedure.

If you have multiple recordsets, you could stored the results into a temporary table and then end the main stored procedure with "SELECT * FROM #TempTable". Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[Maximizer]

Only one (1) stored procedure can be used per report or subreport.

Further, a stored procedure cannot be used in combination with other database objects; and lastly, linking is not possible for stored procedures.

Best regards,

James
Crystal Report(tm) Certified Consultant
CMRC
Crystal Decisions Business Partner
Montreal, Qc, Canada

http://www.cmrc.qc.ca

 

[MalcolmW]

This is a SQL Server sample solution to this problem, using one stored procedure to call one of two others, depending on the value of a parameter passed to the original sp. It is possible to chain numerous sp's together like this. The key is that the result set always has the same structure, and always comes from the original stored procedure, because of the final select statement.
This can be an effective way to reuse complex code in stored procedures - and reduce maintenance by packaging code in stored procedure "modules" that you call multiple times.

CREATE PROCEDURE spForm2 @WhichSPDoICall varchar(50)
AS
SET NOCOUNT ON
--------------------------
CREATE TABLE #TempTable(
EmployeeID int NOT NULL,
StartDate datetime NULL,
TermDate datetime NULL)
--------------------------
IF @WhichSPDoICall = 'spForm2A'
--Populates the # table
EXECUTE spForm2A
ELSE IF @WhichSPDoICall = 'spForm2B
--Populates the # table with some other data instead
EXECUTE spForm2B
SELECT * FROM #TempTable
SET NOCOUNT OFF
-----End of procedure

and a typical called stored procedure would be like
CREATE PROCEDURE spForm2A
AS
SET NOCOUNT ON
--------------------------
--#TempTable has already been created in calling procedure
INSERT INTO #TempTable (EmployeeID, StartDate)
SELECT * FROM SomeTable
Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[alam]

Hey Guys !!
Can u send me any sample of PL/SQL procedure accessible by Crytal reports.I wanna learn to use Oracle stored procedure accessed by Crystal.I tried it and the problem i am facing is that, i am getting the OUT parasmeter also in the Crystal report.I jus want to have few examples and then i think , i can go along with it..If u ppl have any sample pls do send it to me or mail me at
alam_imran@hotmail.com
Thanks in advance
bye.

 

[MalcolmW]

For samples for various versions of Oracle, Crystal Reports, and info on what drivers to use, see

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

Note that with Oracle, you cannot call another stored procedure from a stored procedure.
PS. If you want to get an auto email whenever there is a new posting on a thread, check E-Mail Notification when you are posting in that thread, or click on the email notification at the top right corner of the thread. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net