Set parameter value with CASE WHEN NOT EXISTS always comes out to 0-CodePudding

I have a parameter (@noMatch int output) which I assign a value to based on the CASE found in my stored procedure below:

ALTER PROCEDURE [dbo].[p_trips_non_aggregate_insert]
     @trips u_trips_non_aggregate readonly, 
     @rowCount int OUTPUT, 
     @noMatch int OUTPUT
WITH RECOMPILE
AS
    BEGIN TRANSACTION
    SET NOCOUNT ON

    INSERT INTO [dbo].[trips_non_aggregate]
        ([LøyvehaverID], [KjøretøyID], [Ordrenummer], [c], [År], [Måned], [Betaling (brutto)], [Betaling (netto)], [LøyvehaverFakturaID], [IsProcessed], [RowIsChecked])
        
        SELECT DISTINCT 
            [LøyvehaverID], [KjøretøyID], [Ordrenummer], [c], [År], [Måned], [Betaling (brutto)], [Betaling (netto)], [LøyvehaverFakturaID], [IsProcessed], [RowIsChecked]
        FROM 
            @trips AS o
        WHERE 
            [IsProcessed] = '0'
            AND NOT EXISTS (SELECT [Ordrenummer]
                            FROM [dbo].[trips_non_aggregate] AS i
                            WHERE i.[Ordrenummer] = o.[Ordrenummer])

        SET @rowCount = @@ROWCOUNT

        SET @noMatch = 
            CASE WHEN NOT EXISTS
                (SELECT [LøyvehaverID]
                    FROM [dbo].[trips_non_aggregate] AS i
                    INNER JOIN [license_holder] AS o ON i.[LøyvehaverID] = o.[Foretaksnavn])
            THEN 1 ELSE 0
        END

    UPDATE [dbo].[trips_non_aggregate]
    SET [Betaling (netto)] = (SELECT [Betaling (brutto)] / ([MVAsats] / 100   1)
                              FROM [tblMVAkoder]
                              WHERE [ID] = 'MVAkode2')

    UPDATE [dbo].[trips_non_aggregate]
    SET [RowIsChecked] = (SELECT [RowIsChecked] = 0)

    COMMIT TRANSACTION

So that when I insert to my database, I am able to flag any entry into ColumnA that does not already exist in ColumnB. But when I test with fake data, the outcome is always 0.

Why could that be?

This is how I check its value in C#:

if (Convert.ToInt32(cmd.Parameters["@noMatch"].Value) == 1) 
{
    // do something
}

CodePudding user response：

It looks like you're doing some kind of preflight checking so that you don't insert any data at all from the batch, if one or more incoming u_trips_non_agg records don't have a Licence Holder. We can check before we do the insert:

ALTER PROCEDURE [dbo].[p_trips_non_aggregate_insert]
     @trips u_trips_non_aggregate readonly, 
     @rowCount int OUTPUT
WITH RECOMPILE
AS

    IF EXISTS (
      SELECT null FROM @trips t 
      WHERE NOT EXISTS (
        SELECT null FROM license_holder lh WHERE t.[LøyvehaverID] = lh.[Foretaksnavn]
      )
    ) THEN 
      THROW 51000, 'At least one of the records being inserted does not have a related licence holder record', 1
    END


    BEGIN TRANSACTION
    SET NOCOUNT ON

    INSERT INTO [dbo].[trips_non_aggregate]
        ([LøyvehaverID], [KjøretøyID], [Ordrenummer], [c], [År], [Måned], [Betaling (brutto)], [Betaling (netto)], [LøyvehaverFakturaID], [IsProcessed], [RowIsChecked])
        
        SELECT DISTINCT 
            [LøyvehaverID], [KjøretøyID], [Ordrenummer], [c], [År], [Måned], [Betaling (brutto)], [Betaling (netto)], [LøyvehaverFakturaID], [IsProcessed], [RowIsChecked]
        FROM 
            @trips AS o
        WHERE 
            [IsProcessed] = '0'
            AND NOT EXISTS (SELECT [Ordrenummer]
                            FROM [dbo].[trips_non_aggregate] AS i
                            WHERE i.[Ordrenummer] = o.[Ordrenummer])

        SET @rowCount = @@ROWCOUNT

        SET @noMatch = 
            CASE WHEN NOT EXISTS
                (SELECT [LøyvehaverID]
                    FROM [dbo].[trips_non_aggregate] AS i
                    INNER JOIN [license_holder] AS o ON i.[LøyvehaverID] = o.[Foretaksnavn])
            THEN 1 ELSE 0
        END

    UPDATE [dbo].[trips_non_aggregate]
    SET [Betaling (netto)] = (SELECT [Betaling (brutto)] / ([MVAsats] / 100   1)
                              FROM [tblMVAkoder]
                              WHERE [ID] = 'MVAkode2')

    UPDATE [dbo].[trips_non_aggregate]
    SET [RowIsChecked] = (SELECT [RowIsChecked] = 0)

    COMMIT TRANSACTION

An EXISTS returns true or false depending on whether the query returns any row. It doesn't matter what data is selected; all EXISTS cares about is whether there is any row or not. You can conceive that it stops as soon as it finds one, so it can be quite optimal to not run a massive million rows query just to know if there is one row or not

We actually use it twice, an inner and an outer:

  SELECT null FROM @trips t 
  WHERE NOT EXISTS (
    SELECT null FROM license_holder lh WHERE t.[LøyvehaverID] = lh.[Foretaksnavn]
  )

The first one to run conceptually in the inner one:

SELECT null FROM license_holder lh WHERE t.[LøyvehaverID] = lh.[Foretaksnavn]

This is coordinated with the outer one, and you could conceive it working like:

For `t.[LøyvehaverID]` = 1
SELECT null FROM license_holder lh WHERE 1 = lh.[Foretaksnavn]

For `t.[LøyvehaverID]` = 2
SELECT null FROM license_holder lh WHERE 2 = lh.[Foretaksnavn]

And so on; it's basically, for every row in the outer table @trips checking if there is a row in in licence_holder such that @trips.[LøyvehaverID] equals licence_holder.[Foretaksnavn].

If there is a row, then null is returned. If there is not a row, then 0 rows are returns, and that's what NOT EXISTS picks up on. In other words, this structure:

  FROM @trips t 
  WHERE NOT EXISTS (
    SELECT null FROM license_holder lh WHERE t.[LøyvehaverID] = lh.[Foretaksnavn]
  )

will find all @trips rows that do not have a matching licence_holder row

All that is wrapped in another EXISTS, which effectively stops looking as soon as at least one case of "a @trips row that doesn't have a licence_holder row" is found

In total this:

EXISTS (
  SELECT null FROM @trips t 
  WHERE NOT EXISTS (
    SELECT null FROM license_holder lh WHERE t.[LøyvehaverID] = lh.[Foretaksnavn]
  )
)

means:

there exists a row in @trips where there does not exist a matching row in licence holder

And then we key off of that to throw an exception if it is the case

CodePudding user response：

Is parameter direction configured like

    cmd.Parameters.Add("@noMatch", SqlDbType.Int);  
    cmd.Parameters["@noMatch"].Direction = ParameterDirection.Output;