Stored Procedure Return Values

[2J]

Does anyone know how to capture the return value of a stored procedure in a Crystal Report?<br>
<br>
I want to return a certain value if there are no records available and then report an error message to the user.<br>
<br>
Thanks<br>
2J

 

[LindaC]

Dear 2J,<br>
Have you looked at the function 'OnLastRecord'? It returns a boolean value of 'true' when the current record is the last record. You can access this function in the formula window under 'Other'. Hope this helps.<br>
<br>
LindaC

 

[MalcolmW]

A few ways come to mind, both of which would avoid the problem with the solution that LindaC proposed (false positive if the SP only returned one row). I haven't tested any of these, so free advice is worth what you pay for it...<br>
IF Crystal treats the empty result set of a SP as a record containing null values for all rows (I don't know if it does), then you could do a test for null in a column where you knew you wouldn't get nulls in any other circumstance.<br>
<br>
Another way is to do a count of the rows that will be returned by the SP (in the SP itself), and if the count is zero, produce a row that matches the column structure of the SP, but contains an error flag that you can use to generate a message. Depending on the SP, this could be expensive.<br>
<br>
Lastly, create a formula field in CR that generates an error message, and place it in the Page Header (which prints even when no rows are returned).<br>
<br>
StringVar MyErrorMessage ;<br>
MyErrorMessage := &quot;Error message text here&quot; ;<br>
If RecordNumber = 1 then MyErrorMessage := &quot;&quot;<br>
Else MyErrorMessage<br>
<p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>