|
MS SQL Server stored procedures use a number of ways of communicating information back to the user. One of these is the PRINT statement. This statement is typically used for informational type messages. If the intention is to raise an exception in the calling application, it is preferable to use the RAISERROR statement. Many users want to use the PRINT statement, however, and it has not been possible to access informational type messages with the BDE - only RAISERROR was supported, and then only those errors of sufficient severity to raise an exception. With ADO it is possible to access PRINT statement messages. ADO considers these messages to be errors of severity 0 - too low to raise an exception - but nevertheless the messages are written to ADO's errors collection, which, in Delphi, is a property of the TADOConnection component. Another fact worth pointing out is that Print statement messages are only placed in the Errors collection if the ADO command is executed with the eoExecuteNoRecords Option set to True.
How do we retrieve this message in practice?
create PROCEDURE sp_show_test (@param1 char(30)) as
begin
If (@param1 = "Some Value")
Select * from dbo.authors
else
begin
print "Illegal value : "+@param1
return 25
end
end
This will return a result set if the input parameter is set to "Some Value", otherwise it will issue the "Illegal Value" message and return a user defined return code of 25. How do we deal with this in Delphi? If we just use a TADODataSet component, we risk getting an exception raised when a result set is not returned by the stored procedure. It is therefore better to use a TADOCommand component AND a TADODataSet. The Execute method of a TADOCommand can return an ADO _RecordSet object, which we can assign to the RecordSet property of the TADODataSet. If the stored procedure does not return a result set, the _RecordSet is still returned but in a closed state, which we can test for. Here is a simple Delphi unit that allows us to see the Print statement's message. In addition to the three ADO components listed above this code requires a TEdit and TButton, and 'ADOInt' should be added to your uses clause:
procedure TForm1.Button1Click(Sender: TObject);
var
MyReturn : Integer;
looper : Integer;
MyRecordSet : _RecordSet;
begin
ADOCommand1.Prepared := True;
ADOCommand1.Parameters.ParamByName('@param1').Value := Edit1.Text;
MyRecordSet := ADOCommand1.Execute;
if MyRecordSet.State = adStateOpen then
begin
ADODataSet1.Recordset := MyRecordSet;
ADODataSet1.Open;
end
else
begin
ADOCommand1.ExecuteOptions := [eoExecuteNoRecords];
ADOCommand1.Execute;
MyReturn := ADOCommand1.Parameters.ParamByName('RETURN_VALUE').Value;
for looper := 0 to ADOConnection1.Errors.Count - 1 do
begin
ShowMessage(Format('Stored Procedure %s Failed with Return Code %d and Message "%s"',
[ADOCommand1.CommandText,MyReturn,
TrimRight(ADOConnection1.Errors[looper].Description)]));
end;
end;
end;
As mentioned, when we execute the procedure the first time, we are expecting a result set to be returned. This will mean that the Errors collection will not be filled. If the result set has not been returned, we must execute the procedure a second time with the eoExecuteNoRecords option set. |
Last Modified: 17-DEC-99