INTERSECT in UPDATE sp

David Chase 681 Reputation points
2021-02-03T21:06:26.937+00:00

We are using INTERSECT in our WHERE clause for updating a table to bypass the actual update if nothing changes. It seems to be working in other stored procedures but not this one. Below is our sp and I was testing by not changing anything so @MealName, @Instructions and @Verified were not changed from the values in the table. In the 2nd set of code I have the table schema.

ALTER PROCEDURE [dbo].[kd_updtblActualMealsPartial]
    (@MealID        int
    ,@MealName      nvarchar(75)
    ,@Instructions  nvarchar(4000)
    ,@Verified      bit = 0
    ,@UserLoginID   int
    )

AS 
    BEGIN

        --Put login userid into session context for use in trigger(s)
        EXEC sp_set_session_context 'USERID', @UserLoginID, @read_only = 0;

        DECLARE @WasVerified bit;
        DECLARE @PatientID int;

        SELECT @WasVerified = Verified,
               @PatientID = PatientID
          FROM dbo.tblActualMeals 
         WHERE (MealID = @MealID);

        UPDATE dbo.tblActualMeals 
           SET [MealName] = @MealName, 
               [Instructions] = @Instructions, 
               [Verified] = @Verified 
         WHERE ([MealID] = @MealID)
        AND  NOT EXISTS (SELECT MealID, MealName, ISNULL(Instructions,''), Verified
                        INTERSECT
                        SELECT @MealID, @MealName, ISNULL(@Instructions,''), @Verified);

        --if unverifying meal then remove any MealCalendar records for that MealID
        IF @WasVerified = 1 AND @Verified = 0
            BEGIN
                DELETE FROM dbo.tblMealCalendar 
                       WHERE (PatientID = @PatientID) 
                         AND (MealID = @MealID);
            END
    END

CREATE TABLE [dbo].[tblActualMeals](
    [MealID] [int] IDENTITY(1,1) NOT NULL,
    [PatientID] [int] NULL,
    [MealName] [nvarchar](75) NULL,
    [MealDate] [smalldatetime] NULL,
    [Instructions] [nvarchar](4000) NULL,
    [Verified] [bit] NOT NULL,
    [MealNumber] [int] NULL,
    [Caregiver] [bit] NOT NULL,
 CONSTRAINT [PK_tblActualMeals] PRIMARY KEY NONCLUSTERED 
(
    [MealID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblActualMeals] ADD  CONSTRAINT [DF_tblActualMeals_Verified]  DEFAULT ((0)) FOR [Verified]
GO

ALTER TABLE [dbo].[tblActualMeals] ADD  CONSTRAINT [DF_tblActualMeals_MealNumber]  DEFAULT ((0)) FOR [MealNumber]
GO

ALTER TABLE [dbo].[tblActualMeals] ADD  CONSTRAINT [DF_tblActualMeals_Caregiver]  DEFAULT ((0)) FOR [Caregiver]
GO
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 102.2K Reputation points
    2021-02-04T22:15:54.867+00:00

    Still did not work correctly. I added the FROM to intersect as Melissa suggested and modified the trigger as shown below.

    Melissa fooled you. Her "correction" was incorrect.

    Attached is a script with your tables, procedure and a version of a trigger. It adds one row to the table and then runs the procedure with the same data. No row is returned from the trigger. (Since I don't have the audit table, I commented out the INSERT part.)

    64193-slask.txt

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-04T01:43:24.64+00:00

    Hi @David Chase

    After checking, you still need to complete the select statement before INTERSECT which means that from table part is missing in below part.

    SELECT MealID, MealName, ISNULL(Instructions,''), Verified

    Please refer below updated one:

    UPDATE dbo.tblActualMeals   
    SET [MealName] = @MealName,   
    	[Instructions] = @Instructions,   
    	[Verified] = @Verified   
    WHERE ([MealID] = @MealID)  
    AND  NOT EXISTS (SELECT MealID, MealName, ISNULL(Instructions,''), Verified   
    				from dbo.tblActualMeals   --add this row   
    				INTERSECT  
    				SELECT @MealID, @MealName, ISNULL(@Instructions,''), @Verified);  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Erland Sommarskog 102.2K Reputation points
    2021-02-04T09:09:20.54+00:00

    Depends on what you mean with "the trigger ran". A trigger is always fired, even if the statement did not affect any rows. For this reason, it can often be a good idea to add a quick exit on the top of the trigger:

    IF NOT EXISTS (SELECT * FROM inserted)
       RETURN
    
    0 comments No comments

  3. David Chase 681 Reputation points
    2021-02-04T15:05:22.947+00:00

    Still did not work correctly. I added the FROM to intersect as Melissa suggested and modified the trigger as shown below. I tested running an UPDATE with none of the columns changing and it still created a row in tblActualMealsAudit table.

    ALTER TRIGGER [dbo].[trgActualMealsAudit]
        ON [dbo].[tblActualMeals]
        AFTER INSERT, UPDATE
        AS 
            BEGIN
               SET NOCOUNT ON;
    
               DECLARE @idnumber AS INT;
               SELECT @idnumber = CONVERT (INT, session_context(N'USERID'));
    
               IF EXISTS (SELECT * FROM deleted)
                   BEGIN
                    INSERT INTO [dbo].[tblActualMealsAudit]
                        ([MealID]
                        ,[PatientID]
                        ,[MealName]
                        ,[MealDate]
                        ,[Instructions]
                        ,[Verified]
                        ,[MealNumber]
                        ,[Caregiver]
                        ,[UserLoginID]
                        )
                       SELECT 
                        i.MealID,
                        i.PatientID,
                        i.MealName,
                        i.MealDate,
                        i.Instructions,
                        i.Verified,
                        i.MealNumber,
                        i.Caregiver,
                        @idnumber
                       FROM deleted AS i;
                   END
               ELSE
                   BEGIN
                    IF NOT EXISTS (SELECT * FROM inserted)
                        BEGIN
                            RETURN;
                        END
    
                    INSERT INTO [dbo].[tblActualMealsAudit]
                        ([MealID]
                        ,[PatientID]
                        ,[MealName]
                        ,[MealDate]
                        ,[Instructions]
                        ,[Verified]
                        ,[MealNumber]
                        ,[Caregiver]
                        ,[UserLoginID]
                        )
                       SELECT 
                        i.MealID,
                        i.PatientID,
                        i.MealName,
                        i.MealDate,
                        i.Instructions,
                        i.Verified,
                        i.MealNumber,
                        i.Caregiver,
                        @idnumber
                       FROM inserted AS i;
                   END
           END