Stored Procedure Concerns

Since databases treat stored procedures differently from regular tables, there is an uncommon situations you should be aware of them in case you have a similar circumstance. It involves how you name stored procedure parameters.

If you are using two stored procedures and linking them together, make sure that the stored procedure parameters have different names. As I mentioned earlier, when Crystal Reports creates a new report parameter for a stored procedure, it gives it the same name as the stored procedure parameter name. The problem arises when you have two stored procedures that use the same parameter name. When Crystal Reports creates a new parameter for the second stored procedure, it sees that the report already has a parameter with that name and won’t create a new one. You now have one parameter that is shared between two stored procedures. For a specific parameter such as InventoryNumber, then this is probably fine because you most likely want both stored procedures to filter on the same inventory number. But if you have a generic parameter name such as ID, then this will cause problems. It could represent Customer ID for one stored procedure and Order ID for another stored procedure. Having Crystal Reports pass the same value to both stored procedures would give you very wrong results.

The ironic part is that even though Crystal Reports doesn’t create a new report parameter, when you are building the report Crystal Reports still prompts you to enter the second parameter value. So you think that you are entering two different parameters, but you are really just overwriting the first parameter value with the second value. You won’t realize there is a problem until you run the report. It can be difficult to track down this problem unless you are aware of this quirk. If you need to have a separate report parameter for each stored procedure, you will have to edit the stored procedures directly and rename one of the parameter names so that there is no conflict.