SQL stored proc and Crystal: goofy stuff

[akuchin]

Last few days at work were pretty frustrating for me. I'm designing a Crystal report that is based on SQL stored procedure. I did it many times before and had no problems (what a typical opening for an IT issue, isn't it?).
In the stored procedure I have to use a statement that is similar to the following: EXEC "INSERT INTO [table_name] SELECT * FROM "+table_name_variable+" ". A table name here is the variable, because depending on user input, table name may vary.
If I run stored procedure in Query Analyzer it works well. But if I try to add it to my Crystal report, Crystal returns an error message "ODBC Error:[Microsoft][SQL Server]Incorrect syntax near the keyword 'FROM'". That happens with any procedure - even with the ones that work fine in other reports that I have created. It's happening on all workstations that have Crystal.
I have checked Report options and there are identical in the reports that work and the ones that don't work.
I'm puzzled.

 

[rogar]

Is "table_name_variable" a parameter of the stored procedure? If so, try assigning a default value which is a valid table name.

 

[akuchin]

Rogar,
I got it now!!!! It was a long battle.
As you supposed, the problem did reside in the area of parameters. Table name was driven by a date selection. For example, for selected date 04-01-2002 the table name would be GL060102 where 02 is the year.
Every time I was creating a Crystal report, it would prompt me for a date parameter, but also would have a check-box "SET TO NULL VALUE" on!!! That appears to be my problem.
Thanks for your advice!

 

[Guest_imported]

when I need to pass null as a parameter I used this code
Me.CrystalReport1.StoredProcParam(0) = "crwnull"
or it also prompt me for the parameter.