Stored Procedure Problem

[ulicki]

Hi,

Crystal 7, MS SQL Server 2000. I log on with SA password.

I am responsible for creating a crystal report based on a stored procedure. The stored procedure returns a runtime ID (integer) as an out parameter. There are no in parameters. So the stored procedure is called from the main report, returns this run time ID, which will then be used a paramater to pass to a subreport. In addition the stored procedure populates a table that is used by the subreport. The runtime ID serves so that in the case the report is being run by two different users results are returned correctly for each user.

So I create a new report, add the stored procedure, but there are no fields in the stored procedure to choose. I should be getting the runtime ID. Below is the beginning and end of the stored procedure for review:
BEGIN OF STORED PROCEDURE CODE:
CREATE PROCEDURE ESP_25K_OFFERING
( @p_Runtime_ID INTEGER OUT )
AS
DECLARE
@v_RunDate DATETIME,
@v_Start_Dt DATETIME,
@v_End_Dt DATETIME,
@v_Offer_Num INTEGER,
@v_Previous_Year INTEGER,
@v_Current_Year INTEGER,
@v_Count INTEGER,
@v_Opt_Num INTEGER,
@h_Opt_Num INTEGER,
@v_Calendar_year INTEGER,
@h_Calendar_year INTEGER,
@v_Spans_Years INTEGER,
@v_Previous_Shares INTEGER,
@v_Current_Shares INTEGER, -- shares
@v_Previous_Purchase FLOAT,
@v_Current_Purchase FLOAT,
@h_Look_Back FLOAT,
@v_FMV_Price FLOAT,
@v_SumPurchases FLOAT,
@v_Purchases_Against_Look_back FLOAT,
@vS_Look_Back FLOAT,
@vS_Potential_Purchase FLOAT,
@vS_Potential_Shares INTEGER,
@vS_Current_Purchase FLOAT,
@vS_Current_Shares INTEGER,
@vS_FMV_Price FLOAT,
@vS_Span_Years INTEGER,
@v_Temp FLOAT,
@v_AsOfDate DATETIME,
@v_Look_Back_Year INTEGER,
@v_WhatIDid VARCHAR(35)
BEGIN
SET NOCOUNT ON

END OF STORED PROCEDURE CODE:
SELECT @v_Count = count(*) FROM esp_25k_Summary_rpt WHERE runtime_ID = @p_Runtime_ID
IF @v_Count = 0 BEGIN
EXEC gl_errorHandling
@p_runtime_ID,
'No Data was retrieved',
'ESP_25K_Offering',
501,
'NO_REPORT_DATA',
' '
RAISERROR ( 'We cannot find any grants to process!.',
16, 1 )
RETURN 1
END
SELECT @p_Runtime_ID
END


GO

The runtime ID gets set with the following statement:
SET @p_Runtime_ID = @@IDENTITY

Also I was told the problem may be with the p2ssql.dll. Based on crystal knowledge base article c2005718. I used two versions of the dll with no luck. I thought I mention this, but I am not sure if it relates to the problem.

Thank you for any help,

Mike

 

[ulicki]

Hi,

I just reread my post and want reiterate the problem. Once the stored procedure is added to the report there is no field to add to the report.

Mike

 

[vidru]

You need to change your final SELECT to something like:

SELECT p_Runtime_ID = @p_Runtime_ID

-dave

 

[ulicki]

THAT DID IT!!!!!

Thanks,

Dave

 

[vidru]

And you might want to read this thread: thread183-672637. Good explanations of @@IDENTITY vs. SCOPE_IDENTITY.

-dave

 

[Naith]

There's no gratitude like the gratitude of a poster so indebted to your advice that he changes his name to yours.

 

[ulicki]

LOL