How can I use a dynamic Stored Procedure ?

[Siggy19]

I have multiple parameters that I want to use and each can be set to 'ALL' or a specific value.

Rather than using multiple IF statements in the Stored Procedure to select the correct SQL statement, I am creating a single SQL Statement dynamically and then executing it using the EXECUTE command. (The fields returned by the SQL statement are always the same)

This works fine when I try running it as a Stored Procedure, but Crystal 8.5 will not let me Verify the Database using this query saying that there are no fields in the file.

I tried populating a temporary table using the execute command and that didn't even compile.

Any ideas ?

 

[synapsevampire]

What database are you using?

If it's Oracle, CR is very particular about how an SP is constructed, and what is returned.

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

-k