CODEWITHSUNDEEP
sql-serverdelphistored-procedures
CiucaS
CiucaS

Reputation: 2128

SQL Server Stored Procedure is not raising error in my program

CREATE PROCEDURE [dbo].[spTest]
@Pozitii varchar(max),
@NrZile int

AS   
set @Pozitii = SUBSTRING(@Pozitii,0,LEn(@Pozitii))

CREATE TABLE #Pozitii (part varchar(20) null)



INSERT INTO #Pozitii(part) 
SELECT part
FROM dbo.SDF_SplitString(@Pozitii,',')


if exists (SELECT * FROM #Pozitii)
 RAISERROR('asdf',16,-1)  
else     RAISERROR('else',16,-1)

So runing this SP in SQL like this

exec [spTest] '11,12,13,',1

Returns:

(3 row(s) affected)
Msg 50000, Level 16, State 1, Procedure spTest, Line 27
asdf

Now if I run my procedure in delphi ( using an ADO object) 

procedure TframePlanificatorPozitieComanda.Button5Click(Sender: TObject);
begin


  try
    with dm.spTest do
    begin
      Close;
      Parameters.ParambyName('@Pozitii').Value := '11,12,13,';
      Parameters.ParambyName('@NrZile').Value := 1;
      ExecProc;
    end;
  except
    on E: Exception do
    begin
      ShowMessage(E.Message);
    end;

  end;
end;

This code is not raising any errors?Any ideas why?

Upvotes: 0

Views: 1654

Answers (3)

MartynA
MartynA

Reputation: 30715

Using Sql Server 2014, I don't get the behaviour you describe.

I have a stored proc on the server defined as

CREATE PROCEDURE spRaiseError(@AnError int) 
AS
BEGIN
  declare @Msg Char(20)
  if @AnError > 0 
    begin
      Select @Msg = 'MyError ' + convert(Char(8), @AnError)
      RaisError(@Msg, 16, -1)
    end
  else
    select 1
END

I have a minimalist D7 Ado project with a TAdoConnection, TAdoQuery, TDataSource and TDBGrid connected up as you'd expect, a TEdit and a TButton.

Using this code

procedure TForm1.Button1Click(Sender: TObject);
var
  S : String;
  ErrorCount : Integer;
begin
  S := 'exec spRaiseError ' + Edit1.Text;
  AdoQuery1.SQL.Text := S;
  try
    AdoQuery1.Open;
  except
  end;

  ErrorCount := AdoConnection1.Errors.Count;
  Caption := IntToStr(ErrorCount);

end;

, if the number in Edit1 is > 0 I get only the error number on the form's caption, whereas if it contains 0 I see the value 1 in the DBGrid.

Btw, with "Stop on language exceptions" checked in the D7 debugger settings, without the try/except around AdoQuery1.Open, the debugger sees and catches the exception from the server.

Anyway, the take-home message from this answer is that you can use the TAdoConnection's Errors collection to detect whether there was an error and, if there was, you can get more information from it. See the Delphi OLH amd MS Ado documentation for more info about the Errors collection of TAdoConnection and other Ado-based Delphi classes.

Upvotes: 0

Marwan Almukh
Marwan Almukh

Reputation: 201

The error is raising in sql-server.. but in delphi is not .. because there the procedure is already executed.. But in Delphi you can check if the procedure is run successfully or not regards what the result of the procedure (some of procedures have no output parameters). In delphi please check your adostoredprocedure.parameters[0] like:

showmessage(vartostr(self.ADOStoredProc1.Parameters[0].Value))

.

if the result <> 0 that means error.

Upvotes: 0

GNiessen
GNiessen

Reputation: 56

Have you tried adding:

SET NOCOUNT ON;

to your stored procedure? I think the exception is in a second resultset and is getting hidden by the first select result count.

Upvotes: 3

Related Questions