Crystal Reports using multiple results from a Stor

Question

I have a stored proc in sql-server and one of the parameters it returns is a string with the query parameters. I display those query parameters at the top of the report. That works great if something is found, not so great if nothing was found.

We have tried returning two query results, one the data set that I will make the report from (which includes the query parameters), the other the query parameter string. Crystal appears to only see the first data set, and this very old discussion (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42462) says that is not something that will work. But that was over 5 years ago, and I'm hoping things have changed.

The problem, is if nothing is returned, the report is so blank that the person doesn't even know what query parameters they used. If they could see that they queried something that doesn't return any results, that would be useful.

So, if I have at the end of my stored proc:

SELECT * FROM [#ResultSet]
select @SearchCriteria as SearchCriteria

I'd like to be able to display the SearchCriteria even if there is nothing in the #ResultSet. Can it be done with this version of Crystal? Is there another way to do this?

Answer 1

Unless as stated by the first answer the results of one procedure have the same number of columns of another procedure (this includes type), if this is the case you can UNION the results or UNION ALL the results (if you want duplicates) to get ONE resultant set.

If the types or columns are not the same then you cannot do this. The only other option you can do is to merge all the relevant data into a temp table and then return the results from that temp table (SELECT * FROM #temp)

Answer 2

How are you currently able to display the parameters when results are found?

You haven't mentioned how you are using the Crystal Report in your environment.

Typically, I've done criteria display by passing the parameters to the Crystal Report as Report Parameters, and then using them in fields. This assumes you are calling it from a client application in some way.

Another option is to load the results into client datatables and binding to that as a datasource, it's certainly possible to handle the multiple result sets that way.