Executing multiple stored procedures is causing an error [using Transaction]

Question

Something really weird is happening with me with SQL Server Management Studio.

I created a stored procedure and then I execute this stored procedure (in a row) using different parameters on each call.

Then I get an error like I am trying to insert a value in a table using the same primary key, this should not happen because I have a condition that tries to avoid that.

Also I have each block of code inside stored procedure inside a transaction.

I have some questions, how does SQL Server Management Studio trigger all the lines of code? It parses and run each line separately right? On a sequence... I suppose. There isn't any multiple threads running multiple stored procedures at the same time in this case, I suppose.

I am just selecting 4+ lines of stored procedures executes and the error appears.

I tried to execute each stored procedure call in a single way, and I don't get any error. So what the hell is this? I suppose my code is okay, but maybe you should check this out:

USE mydatabase;
GO

IF EXISTS (SELECT * FROM sys.objects 
           WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('[myschema].[CategoriesFields]'))
     EXEC('DROP PROCEDURE [myschema].[CategoriesFields]')

IF NOT EXISTS (SELECT * FROM sys.objects 
               WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('[myschema].[CategoriesFields]'))
     EXEC('CREATE PROCEDURE [myschema].[CategoriesFields]
        @CategoryParentName NVARCHAR(256),
        @CategoryName NVARCHAR(256),
        @FieldCode NVARCHAR(64) 
        AS  
        BEGIN
        SET NOCOUNT ON; 
        BEGIN TRY

        BEGIN TRANSACTION [InsertCategoryTrans];

        DECLARE @CategoryId int;                               

        DECLARE @CategoryParentId int;
            IF @CategoryParentName IS NOT NULL 
                SELECT @CategoryParentId = Id FROM [mydatabase].[myschema].[Categories] WHERE CategoryName = @CategoryParentName

            --INSERT CATEGORY
            IF @CategoryParentId IS NULL 
                IF NOT EXISTS (SELECT * FROM [mydatabase].[myschema].[Categories] WHERE CategoryName = @CategoryName AND CategoryParentId IS NULL)
                        INSERT INTO [mydatabase].[myschema].[Categories] (CategoryName, CategoryParentId)
                        VALUES (@CategoryName, @CategoryParentId)
            ELSE                
                IF NOT EXISTS (SELECT * FROM [mydatabase].[myschema].[Categories] WHERE CategoryName = @CategoryName AND CategoryParentId = @CategoryParentId)                      
                        INSERT INTO [mydatabase].[myschema].[Categories] (CategoryName, CategoryParentId)
                            VALUES (@CategoryName, @CategoryParentId)                                               

            SET @CategoryId = @@IDENTITY;

            IF @CategoryId IS NULL
                SELECT @CategoryId = Id FROM [mydatabase].[myschema].[Categories] WHERE CategoryName = @CategoryName



            IF @FieldCode IS NOT NULL AND NOT EXISTS (SELECT * FROM [mydatabase].[myschema].[Fields] WHERE CategoryId = @CategoryId AND FieldCode = @FieldCode)
                INSERT INTO [mydatabase].[myschema].[Fields] (CategoryId, FieldCode)
                    VALUES (@CategoryId, @FieldCode)    

        COMMIT TRANSACTION [InsertCategoryTrans];
    END TRY 
    BEGIN CATCH 
        ROLLBACK TRANSACTION [InsertCategoryTrans]; 
        SELECT ERROR_MESSAGE() AS [Message], ERROR_LINE() AS [Line] 
    END CATCH
    END');


EXEC [mydatabase].[myschema].[CategoriesFields] NULL, 'CategoryA', 'FieldExample'
EXEC [mydatabase].[myschema].[CategoriesFields] NULL, 'CategoryA', 'FieldExample2'

EXEC [mydatabase].[myschema].[CategoriesFields] 'CategoryA', 'Child_CategoryOfA', 'Arruamento'


EXEC [mydatabase].[myschema].[CategoriesFields] NULL, 'CategoryB', 'AnotherField'
EXEC [mydatabase].[myschema].[CategoriesFields] NULL, 'CategoryB', 'AnotherField2'
EXEC [mydatabase].[myschema].[CategoriesFields] 'CategoryB', 'Child_CategoryOfB', 'AnotherField4'
EXEC [mydatabase].[myschema].[CategoriesFields] 'CategoryB', 'Child_CategoryOfB', 'AnotherField5'


EXEC [mydatabase].[myschema].[CategoriesFields] NULL, 'CategoryC', NULL
EXEC [mydatabase].[myschema].[CategoriesFields] 'CategoryC', 'ChildOfC_Category12', 'Field2'
EXEC [mydatabase].[myschema].[CategoriesFields] 'CategoryC', 'ChildOfC_Category13', 'Field3'
EXEC [mydatabase].[myschema].[CategoriesFields] 'CategoryC', 'ChildOfC_Category14', 'Field4'
EXEC [mydatabase].[myschema].[CategoriesFields] 'CategoryC', 'ChildOfC_Category15', 'Field5'
EXEC [mydatabase].[myschema].[CategoriesFields] 'CategoryC', 'ChildOfC_Category15', NULL


EXEC [mydatabase].[myschema].[CategoriesFields] 'ChildOfC_Category15', 'ChildOf_ChildOfC_Category15', 'Detail'
EXEC [mydatabase].[myschema].[CategoriesFields] 'ChildOfC_Category15', 'ChildOf_ChildOfC_Category15', 'AllowedRoles'
EXEC [mydatabase].[myschema].[CategoriesFields] 'ChildOfC_Category15', 'ChildOf_ChildOfC_Category15', 'RelatedCompanies'

EXEC [mydatabase].[myschema].[CategoriesFields] 'FieldLabels', 'Company', 'Identifier'
EXEC [mydatabase].[myschema].[CategoriesFields] 'FieldLabels', 'Company', 'Name'

EXEC [mydatabase].[myschema].[CategoriesFields] 'Common', 'FieldLabels', 'Identifier'
EXEC [mydatabase].[myschema].[CategoriesFields] 'Common', 'FieldLabels', 'Code'
EXEC [mydatabase].[myschema].[CategoriesFields] 'Common', 'FieldLabels', 'Name'
EXEC [mydatabase].[myschema].[CategoriesFields] 'Common', 'ButtonLabels', 'Code'
EXEC [mydatabase].[myschema].[CategoriesFields] 'Common', 'Messages', 'Name'

The error I get when multiple selecting all the lines and executing all is the following:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_myschema.Fields_myschema.Categories_CategoryId". The conflict occurred in database "mydatabase", table "myschema.Categories", column 'Id'.

Thank you in advance!

http://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint — Kumar_Vikas, Dec 03 '16 at 04:45
that doesnt help, please read the question. I can run each execute one by one. only in bulk I can't.... — TiagoM, Dec 03 '16 at 04:46
Have you tried using a 'GO' command after each of the exec commands? — khaled4vokalz, Dec 03 '16 at 06:00

score 0 · Answer 1 · answered Dec 06 '16 at 17:16

Well the problem was fixed changing the way I was commiting the transactions, and separating a single transaction to two different transactions, here is the resulting code if someone needs

EXEC('CREATE PROCEDURE [myschema].[CategoriesFields]
        @CategoryParentName NVARCHAR(256),
        @CategoryName NVARCHAR(256),
        @FieldCode NVARCHAR(64) 
        AS  
        BEGIN
        SET NOCOUNT ON; 
        BEGIN TRY

        BEGIN TRANSACTION [InsertCategoryTrans];

        DECLARE @CategoryId int;                               

        DECLARE @CategoryParentId int;
            IF @CategoryParentName IS NOT NULL 
                SELECT @CategoryParentId = Id FROM [mydatabase].[myschema].[Categories] WHERE CategoryName = @CategoryParentName

            --INSERT CATEGORY
            IF @CategoryParentId IS NULL
            BEGIN
                IF NOT EXISTS (SELECT * FROM [mydatabase].[myschema].[Categories] WHERE CategoryName = @CategoryName AND CategoryParentId IS NULL)
                        INSERT INTO [mydatabase].[myschema].[Categories] (CategoryName, CategoryParentId)
                        VALUES (@CategoryName, @CategoryParentId)
            END
            ELSE
            BEGIN                
                IF NOT EXISTS (SELECT * FROM [mydatabase].[myschema].[Categories] WHERE CategoryName = @CategoryName AND CategoryParentId = @CategoryParentId)                      
                        INSERT INTO [mydatabase].[myschema].[Categories] (CategoryName, CategoryParentId)
                            VALUES (@CategoryName, @CategoryParentId)                                               
            END

            SET @CategoryId = @@IDENTITY;

            IF @CategoryId IS NULL
                SELECT @CategoryId = Id FROM [mydatabase].[myschema].[Categories] WHERE CategoryName = @CategoryName

            COMMIT TRANSACTION [InsertCategoryTrans];

            IF @FieldCode IS NOT NULL AND NOT EXISTS (SELECT * FROM [mydatabase].[myschema].[Fields] WHERE CategoryId = @CategoryId AND FieldCode = @FieldCode)
            BEGIN
               BEGIN TRANSACTION [InsertCategoryFieldTrans];
                  INSERT INTO [mydatabase].[myschema].[Fields] (CategoryId, FieldCode)
                    VALUES (@CategoryId, @FieldCode)    
               COMMIT TRANSACTION [InsertCategoryFieldTrans];
            END

    END TRY 
    BEGIN CATCH 
        ROLLBACK TRANSACTION [InsertCategoryTrans]; 
        SELECT ERROR_MESSAGE() AS [Message], ERROR_LINE() AS [Line] 
    END CATCH
    END');

Executing multiple stored procedures is causing an error [using Transaction]

1 Answers1