Executing a Stored Procedure (with feedback) instead of delete

[sLesae]

Hi,

In our application we need to execute a Stored Procedure instead of simply deleting a record. This stored procedure will check if deleting the record is allowed, and if that is the case, it will delete the necessary records. But if the prerequisites are not met, our Stored Procedure returns a certain RETURN_VALUE and a message in a @Message parameter. This is what the stored Procedure looks like

Code: Select all

-- =============================================
-- Author:		sLesage
-- Create date: 20121106
-- Description:	Deletes a dagtaak, its details and maybe also the Opdracht
-- =============================================
ALTER PROCEDURE P_DELETE_DAGTAAK 
	-- Add the parameters for the stored procedure here
	@DagtaakId INT,
	@Message   NVARCHAR( max ) OUTPUT
AS
BEGIN
  DECLARE @OpdrachtId INT
  
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	IF ( EXISTS ( SELECT DagtaakId FROM T_DAGTAAK TD WHERE DagtaakId = @DagtaakId AND StatusId <= 7 ) )
	BEGIN
	  -- We have a Dagtaak with the given ID which isn't in process yet.
	  IF ( EXISTS ( SELECT OpdrachtId FROM T_OPDRACHT TO1 WHERE DagtaakId = @DagtaakId AND StatusId  > 6) )
	  BEGIN
	    SET @Message = 'Kan de dagtaak niet verwijderen omdat de bijhorende opdracht reeds werd gestart'
	    PRINT @Message
	    RETURN -1
	  END

	  SELECT @OpdrachtId = OpdrachtId FROM T_OPDRACHT TO1 WHERE DagtaakId = @DagtaakId AND StatusId  <= 6
	  
	  IF ( @@rowcount = 1 )
	  BEGIN
	    DELETE FROM T_OPDRACHT_DETAIL WHERE OpdrachtId = @OpdrachtId
	    DELETE FROM T_OPDRACHT WHERE OpdrachtId = @OpdrachtId
	  END
	  
	  DELETE FROM T_DAGTAAK_DETAIL WHERE DagtaakId = @DagtaakId
	  DELETE FROM T_DAGTAAK WHERE DagtaakId = @DagtaakId
	END
	ELSE
	BEGIN
	  IF ( EXISTS(  SELECT DagtaakId FROM T_DAGTAAK TD WHERE DagtaakId = @DagtaakId ) )
	  BEGIN
	    SET @Message = 'Kan de dagtaak niet verwijderen omdat de dagtaak reeds werd gestart'
	    PRINT @Message
	    RETURN -2
	  END
	  ELSE
	  BEGIN
	    SET @Message = 'Kan de dagtaak niet terugvinden'
	    PRINT @Message
	    RETURN -3
	  END
	END
END

I know I could use the following SQLDelete statement

Code: Select all

{:RETURN_VALUE = CALL P_DELETE_DAGTAAK;1 (:DagtaakId, :Message)}

But I was wondering if I can react to that. If RETURN_VALUE < 0 I would like to display the message received from the Stored Procedure in a dialog box. Does anyone know if that would be possible and how I could achieve that ?

Thanks a lot in advance,


Stefaan

[AlexP]

Hello,

You will not be able to use such procedure in the SQLDelete property, as procedure parameters must match query fields. However, if you use the RAISERROR method (with severity set to 11-18) as parameters in the procedure instead of generating and returning a message about the problem, then, when executing your procedure and error appearing, MS SQL Server will generate an exception, that you can intercept and handle in your application.