sql-serverstored-proceduresfaultfault-tolerance

Return a reject table from a stored procedure


I want my stored procedure to fill a 'reject' table for the line from my staging and which can not be injected in my target table (for example a line without description which is NOT NULL in my target).

I don't have an idea about the structure of the reject table.

I can edit if you need more info!

There is my table staging

CREATE TABLE [staging].[B]
(
    [ID] [varchar](250)  NULL,
    [Address] [varchar](250) NULL,
    [Approved] [bit]  NULL,
    [Description] [varchar](1000) NULL;
)

And this is my target table:

CREATE TABLE [dbo].[A]
(
    [ID] [varchar](250) NOT NULL,
    [Address] [varchar](250) NULL,
    [Approved] [tinyint] NULL,
    [Description] [varchar](1000) NOT NULL;
)

This is my stored procedure code:

  ALTER PROCEDURE [dbo].[StoredProcedure]
    (
        @processType Varchar(4),
        @stagingTable Varchar(64),
        @destinationTable Varchar(64),
        @errorMessage NVarchar(MAX) = null OUTPUT
    )
AS
BEGIN

   SET NOCOUNT ON;

   DECLARE @isLocaltran BIT = 0

   BEGIN TRY
   
    IF @@TRANCOUNT = 0
    BEGIN
        BEGIN TRANSACTION
        SET @isLocaltran = 1
    END
    DELETE 
    FROM  staging.B
    WHERE staging.B.[Description] IS NULL 
    OUTPUT 
    deleted.* 
    INTO [dbo].[RejectTable]

    MERGE dbo.A AS TARGET
    USING staging.B AS SOURCE ON (TARGET.ID = SOURCE.ID)
       SELECT 
           TARGET.[Address],
           TARGET.[Approved],
           TARGET.[Description]

       EXCEPT

       SELECT  
           SOURCE.[Address],
           SOURCE.[Approved],
           SOURCE.[Description]
                                )
         THEN
            UPDATE 
                SET TARGET.[Address] = SOURCE.[Address],
                    TARGET.[Approved] = SOURCE.[Approved],
                    TARGET.[Description] = SOURCE.[Description] 
               
    WHEN NOT MATCHED 
        THEN
            INSERT (,[ID], [Address], [Approved], [Description])
            VALUES (...)

)


Solution

  • Thanks very much for @Dan Guzman's comment. It helped me so much.

    DELETE FROM Staging.b OUTPUT deleted.* INTO RejectTable WHERE..