Following up on a question I posted months ago ... I created a stored procedure which checks a few things before performing some database actions. If the checks fail ... the stored procedure raises an error with RAISEERROR which I was hoping to catch in my Delphi code. The StoredProcedure looks like this :
Code: Select all
-- =============================================
-- Author: sLesage
-- Create date: 20121106
-- Description: Deletes a dagtaak, its details and maybe also the Opdracht
-- =============================================
ALTER PROCEDURE [dbo].[P_DELETE_DAGTAAK]
-- Add the parameters for the stored procedure here
@DagtaakId INT
, @Message NVARCHAR(MAX) OUTPUT
AS
BEGIN
DECLARE @OpdrachtId INT
DECLARE @ArtikelId INT
DECLARE @Omschrijving NVARCHAR(MAX)
DECLARE @Aantal udtQuantity
DECLARE @ReturnCode INT = 0
DECLARE @TranCounter INT = 0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @TranCounter = @@trancount
/* First check rerequisites for all DagtaakDetail lines */
DECLARE @DagtaakDetailId INT;
DECLARE crDagtaakDetails CURSOR FAST_FORWARD READ_ONLY FOR
SELECT TDD.DagtaakDetailId
, TDD.ArtikelId
, am.omschr_intern
, TDD.AantalToDo
FROM T_DAGTAAK_DETAIL TDD
JOIN Artikel_magazijn am
ON TDD.ArtikelId = am.Id
WHERE TDD.DagtaakId = @DagtaakId
BEGIN TRY
IF (@TranCounter > 0)
BEGIN
SAVE TRANSACTION ProcedureSavePoint
END
ELSE
BEGIN
BEGIN TRANSACTION
END
/* First check all PreRequisites for Dagtaak Detail */
OPEN crDagtaakDetails;
FETCH NEXT FROM crDagtaakDetails INTO @DagtaakDetailId, @ArtikelId, @Omschrijving, @Aantal;
WHILE (@@FETCH_STATUS = 0) AND (@ReturnCode = 0)
BEGIN
EXEC @ReturnCode = P_DELETE_DAGTAAK_DETAIL @DagtaakDetailId = @DagtaakDetailId
, @Message = @Message OUTPUT
IF (@ReturnCode <> 0)
BEGIN
SET @Message = 'Het wissen van dagtaak ' + CONVERT( VARCHAR(20), @DagtaakId ) + ' faalde! ' + CHAR( 13 ) + CHAR( 10 ) + CHAR( 13 ) + CHAR( 10 ) +
'Kon volgende detail lijn niet wissen : ' + CHAR( 13 ) + CHAR( 10 ) + CHAR( 13 ) + CHAR( 10 ) +
'DagtaakDetailId : ' + CONVERT( VARCHAR(20), @DagtaakDetailId ) + CHAR( 13 ) + CHAR( 10 ) +
'Artikel Id : ' + CONVERT( VARCHAR(20), @ArtikelId ) + CHAR( 13 ) + CHAR( 10 ) +
'Omschrijving : ' + +@Omschrijving + CHAR( 13 ) + CHAR( 10 ) +
'Aantal : ' + CONVERT( VARCHAR(20), @Aantal ) + CHAR( 13 ) + CHAR( 10 ) +
CHAR( 13 ) + CHAR( 10 ) +
@Message
END
FETCH NEXT FROM crDagtaakDetails INTO @DagtaakDetailId, @ArtikelId, @Omschrijving, @Aantal;
END
CLOSE crDagtaakDetails;
DEALLOCATE crDagtaakDetails;
IF (@ReturnCode <> 0)
BEGIN
RAISERROR (@Message, 16, 2)
END
/* If we got here we can delete the DagtaakDetail Lines without any problems so we should check prerequisites for deleting the Dagtaak Itself */
EXEC @ReturnCode = P_DELETE_DAGTAAK_PREREQ @DagtaakId = @DagtaakId
, @Message = @Message OUTPUT
IF (@ReturnCode <> 0)
BEGIN
SET @Message = 'Het wissen van dagtaak ' + CONVERT( VARCHAR(20), @DagtaakId ) + ' faalde ' + @Message
RAISERROR (@Message, 16, 2)
END
DELETE FROM T_OPDRACHT
WHERE DagtaakId = @DagtaakId
DELETE FROM T_DAGTAAK
WHERE DagtaakId = @DagtaakId
IF (@TranCounter = 0)
BEGIN
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
IF (@TranCounter = 0)
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
IF (XACT_STATE( ) <> -1)
BEGIN
ROLLBACK TRANSACTION ProcedureSavePoint
END
END
-- After the appropriate rollback, echo error
-- information to the caller.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE( );
SELECT @ErrorSeverity = ERROR_SEVERITY( );
SELECT @ErrorState = ERROR_STATE( );
--PRINT 'Error ' + CONVERT( VARCHAR(50), ERROR_NUMBER( ) ) +
--', Severity ' + CONVERT( VARCHAR(5), ERROR_SEVERITY( ) ) +
--', State ' + CONVERT( VARCHAR(5), ERROR_STATE( ) ) +
--', Line ' + CONVERT( VARCHAR(5), ERROR_LINE( ) )
--PRINT ERROR_MESSAGE( );
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
RETURN @ReturnCode
END CATCH;
END
Code: Select all
procedure TdtmDagtaak.DeleteDagtaak(const aDagtaakId: Integer);
var
aMessage : String;
begin
stpDeleteDagtaak.ParamByName( 'DagtaakId' ).AsInteger := aDagtaakId;
stpDeleteDagtaak.ExecProc;
if ( stpDeleteDagtaak.ParamByName( 'RETURN_VALUE' ).AsInteger <> 0 ) then
begin
aMessage := stpDeleteDagtaak.ParamByName( 'Message' ).AsString;
raise EWMSDatabaseError.Create(aMessage);
end;
end;
This is pretty strange, because If I call that same stored procedure in the SQLDelete property of a query :
Code: Select all
EXEC P_DELETE_DAGTAAK @DagtaakId = :Old_DagtaakId, @Message = ''
Is there any reason which it doens't behave the same way using a TMSStoredProc component ? Should I maybe change some settings or properties ?
It is very strange, since replacing the TMSStoredProc by a TMSQuery and using EXECSQL on that Query does result in a Delphi Exception.