MS SQL Server 7 Stored Procedure PRINT Statements with ADO

By: Steve Axtell

Abstract: How to access the message from an MS SQL Server 7 Stored Procedure

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?

Consider the following Stored Procedure:

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.

Server Response from: ETNASC04