Watch, Follow, &
Connect with Us

QualityCentral

Public Report
Report From:    [ Add a report in this area ]  
Report #:  15527   Status: Reported
SQL Server Stored Procedures don't need @RETURN_VALUE
Project:   Build #:  9.0.* (all)
Version:    9.0 Submitted By:   Bob Swart
Report Type:  Basic functionality failure Date Reported:  8/30/2005 12:43:15 AM
Severity:    Commonly encountered problem Last Updated: 10/4/2005 4:41:35 AM
Platform:    All platforms Internal Tracking #:  
Resolution: None  Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: (1 Total Rating)
5.00 out of 5
Total Votes: None
Description
SQL Server / MSDE Stored Procedures that return a result, get a parameter in their parameter list called @RETURN_VALUE.
However, this @RETURN_VALUE is never assigned with the actual return value, so the result is always empty.

If you remove the @RETURN_VALUE from the list of parameters (from the BdpCommand), then the stored procedure will work fine, and the result is received OK.

If a stored procedure has no other parameters but the @RETURN_VALUE, then removing it has no effect (if no parameters exist, then Delphi will add them again). This will actually cause an exception "syntax error or access violation".
Steps to Reproduce:
Use the Northwind example database, and the Stored Procedure dialog (or even the Data Explorer!) to execute a stored procedure. The stored procedures from the Northwind database all return a result, and all get the @RETURN_VALUE parameter added to the Parameters list. They will always return an empty result.

If you manually remove the @RETURN_VALUE from the Parameters property of the BdpCommand, then running the stored procedure gives you the right result.

This won't work for the stored procedure called "dbo.Ten Most Expensive Products", since that one only has one parameter: the @RESULT_VALUE. If we call this stored procedure we actually get an error that says  Syntax error or access violation .
And if we try to delete this parameter from the Parameters collection, the Delphi 2005 will dutyfully detect and add all parameters it can find.
Workarounds
For those SQL Server / MSDE stored procedures that return a result and have more than just the result parameter, edit the Parameters property of the BdpCommand and remove the @RETURN_VALUE from the list of Parameters.
Then, you can execute the Stored Procedure just fine again.
Attachment
None
Comments

None

Server Response from: ETNACODE01

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.