CR not passing Stored Proc Parameter?

[samwscott]

SQL2000 Database
CR PRO 8.5

I'm new at this Crystal thing. I'm having trouble getting data from any of my SQL stored procedures. I created a simple stored proc to test:


CREATE PROCEDURE getSurvey
@survid bigint
AS select *
from survey
where surveyid = @survid


I create a new blank report and add my stored procedure. Crystal recognizes the parameter field '@survid'. When I preview the report, there's no data. That is expected b/c @survid is null. When I refresh the report and enter a value for survid, I still get nothing back. If I hardcode the @survid value in the stored proc (or remove the where clause), the report shows the data.

Selecting 'Show SQL Query' from the 'Database' menu gives you:

{call avi.dbo.getSurvey;1(?)}


This looks correct to me and when I paste this into the Crystal SQL Designer with a value for '?' the stored proc works just fine...

{call avi.dbo.getSurvey;1(24)}

So... It seems to me that Crystal is passing a null to SQLServer for the @survid parameter. Is there something I'm missing? Do I have to run the 'Select Expert'? How do I fix this!?

Thanks for your help,
Sam

 

[synapsevampire]

Parameters work fine with Crystal.

Check your SP by setting the value within the stored procedure with a SET.

Also try changing the bigint to integer, perhaps it has something to do with that, I only have SQL Server 7, which doesn't support that type.

-kai