2
\$\begingroup\$

I have the following SQL. I want to make that the SP should work under load. Means it should not have any synchronization issues.

ALTER PROCEDURE [dbo].[DeleteOldDeviceID]
(
    @OldDeviceID VARCHAR(500)
    ,@NewDeviceID  VARCHAR(500)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TranCount INT;
SET @TranCount = @@TRANCOUNT;

BEGIN TRY
    IF @TranCount = 0
        BEGIN TRANSACTION
    ELSE
        SAVE TRANSACTION DeleteOldDeviceID;

    IF @NewDeviceID <> '-1' AND NOT EXISTS(SELECT 1 FROM DeviceCatalog WHERE [UniqueID] = @NewDeviceID) 
        BEGIN 
        INSERT INTO [DeviceCatalog]
                ([os]
                ,[uniqueid]
                ,[address]
                ,[location]
                ,[culture]
                ,[city]
                ,[country]
                ,[other]
                ,[lastmodifieddate]
                ,[createddate]
                ,[IsActive]
                ,[IPAddress]
                ,[NativeDeviceID]
                ,[IsDeleted])
        SELECT  [os]
            ,@NewDeviceID
            ,[address]
            ,[location]
            ,[culture]
            ,[city]
            ,[country]
            ,[other]
            ,GETDATE()
            ,GETDATE()
            ,[IsActive]
            ,[IPAddress]
            ,[NativeDeviceID]
            ,[IsDeleted]
        FROM    [DeviceCatalog]
        WHERE   [UniqueID] = @OldDeviceID;
    END
    DELETE FROM DeviceCatalog WHERE [UniqueID] = @OldDeviceID; -- Always Delete old one
        LBEXIT:
    IF @TranCount = 0
        COMMIT;
END TRY
BEGIN CATCH
    DECLARE @Error INT, @Message VARCHAR(4000), @XState INT;
    SELECT  @Error = ERROR_NUMBER() ,@Message = ERROR_MESSAGE() ,@XState = XACT_STATE();

        IF @XState = -1
            ROLLBACK;
        IF @XState = 1 AND @TranCount = 0
            rollback
        IF @XState = 1 AND @TranCount > 0
            ROLLBACK TRANSACTION DeleteOldDeviceID;

        RAISERROR ('DeleteOldDeviceID: %d: %s', 16, 1, @error, @message) ;
    END CATCH
 END    

Edit:, Is this equal to above SQL?

ALTER PROCEDURE [dbo].[DeleteOldDeviceID]
(
    @OldDeviceID VARCHAR(500)
    ,@NewDeviceID  VARCHAR(500)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TranCount INT;
    SET @TranCount = @@TRANCOUNT;

    BEGIN TRY
        IF @TranCount = 0
            BEGIN TRANSACTION
        ELSE
            SAVE TRANSACTION DeleteOldDeviceID;

        IF @NewDeviceID <> '-1' AND NOT EXISTS(SELECT 1 FROM DeviceCatalog WHERE [UniqueID] = @NewDeviceID) 
            BEGIN   
                UPDATE  [DeviceCatalog]
                SET [UniqueID] = @NewDeviceID
                WHERE   [UniqueID] = @OldDeviceID;
            END
        ELSE
        BEGIN
            DELETE FROM DeviceCatalog WHERE [UniqueID] = @OldDeviceID; -- Always Delete old one
        END
        LBEXIT:
            IF @TranCount = 0
                COMMIT;
    END TRY
    BEGIN CATCH
        DECLARE @Error INT, @Message VARCHAR(4000), @XState INT;
        SELECT  @Error = ERROR_NUMBER() ,@Message = ERROR_MESSAGE() ,@XState = XACT_STATE();
            IF @XState = -1
                ROLLBACK;
            IF @XState = 1 AND @TranCount = 0
                ROLLBACK
            IF @XState = 1 AND @TranCount > 0
                ROLLBACK TRANSACTION DeleteOldDeviceID;

            RAISERROR ('DeleteOldDeviceID: %d: %s', 16, 1, @error, @message) ;
    END CATCH
END
\$\endgroup\$
3
\$\begingroup\$

No, the Second Query won't Delete anything.

In the statement prior to the delete you change the oldID to the newID so there aren't any records with the oldID in that table.


Difference between the two Queries

The first Query, inserts new records into the table, then deletes the records with the oldID

The Second Query, updates all the records removing the oldID's and replacing it with the newID's, so instead of inserting and then deleting the old records, it updates the old records UniqueID column

These both do very different things.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.