SQL Server / MSDE Stored Procedures with BDP
8/30/2005 3:27:54 PM
Yesterday and today I was working on a new article for The Delphi Magazine about the use of Stored Procedures in Delphi (read the October issue for the full story).
I use SQL Server / MSDE with the Northwind database as playground for my examples. All Stored Procedures in the Northwind database return a result, but funnily enough when I used the new Stored Procedure dialog of Delphi 2005 to execute them, I only got empty results.
Calling the Stored Procedures using a BdpCommand did give the expected result, with the exception of a Stored Procedure that only contains one parameter: the result (called @RETURN_VALUE). This last Stored Procedure gave (and still gives) me an error message telling me syntax error or access violation.
Hmm.. so perhaps it's the @RETURN_VALUE parameter, I thought. Using the Parameters collection editor of the BdpCommand, I removed the @RETURN_VALUE parameter from the list of parameters for each Stored Procedure, and guess what: the Stored Procedure dialog now gives me the expected results!
With the exception of the last one again (dso.Ten Most Expensive Products), since removing the parameter from that one only results in Delphi adding it again (by default).
So I learned two things:
As a workaround for the parameter-less Stored Procedures that return a result, I found out that adding a dummy parameter to their definition in SQL Server / MSDE is enough.
- SQL Server / MSDE Stored Procedures can be tested with the Stored Procedure dialog if we first remove the @RETURN_VALUE parameter (if any) from the Parameters list of the BdpCommand.
- SQL Server / MSDE Stored Procedures that only return a result, but have no other parameters, cannot be executed from the Stored Procedure dialog, or using a BdpCommand (but using a SqlCommand they work fine).
However, it's still a bug in the way BDP handles this, so I've filed a Quality Central report, number #15527.
Note that it's only a problem with SQL Server / MSDE. When using InterBase or DB2, you never get a @RETURN_VALUE parameter anyway.