Unable to insert record from user-defined into Table

Question

Scenario

I've created a store procedure in which I'm taking a User-Defined table as a parameter then First I'll check if Role_ID and Form_ID is exists then I'll update my existing table. Otherwise I'll insert into existing table.

Problem

After writing insert query in my store procedure I got below error while altering my store procedure.

Msg 137, Level 16, State 1, Procedure SP_SETUP_ROLES_RIGHTS_SAVE_AND_UPDATE, Line 17 Must declare the scalar variable "@Temp". Msg 137, Level 16, State 1, Procedure SP_SETUP_ROLES_RIGHTS_SAVE_AND_UPDATE, Line 17 Must declare the scalar variable "@Temp".

Below is my SP code:

CREATE TYPE _ROLERIGHTSSCHEMA as Table (
    _Role_ID int,
    _Form_ID int,
    _Form_Name varchar(100),
    _Can_View bit,
    _Can_Edit bit,
    _Can_Prepare_By bit,
    _Can_Change_Status_By bit,
    _Prepared_By_ID int,
    _Prepared_Date datetime
)

ALTER PROCEDURE SP_SETUP_ROLES_RIGHTS_SAVE_AND_UPDATE
(@Temp _ROLERIGHTSSCHEMA ReadOnly)
AS
BEGIN
    IF EXISTS(SELECT * FROM ROLE_RIGHTS WHERE Role_ID = @Temp.Role_ID AND Form_ID = @Temp.Form_ID)
    BEGIN
        Update Role_Rights
        set Can_View = t._Can_View,
            Can_Edit = t._Can_Edit,
            Can_Prepare_By = t._Can_Prepare_By,
            Can_Change_Status_By = _Can_Change_Status_By,
            Modified_By_ID = 0,
            Modified_Date = GETDATE()
            From @Temp t
            WHERE Role_Rights.Role_ID = t._Role_ID and Role_Rights.Form_ID = t._Form_ID
        END
    ELSE
    BEGIN
        INSERT INTO Role_Rights (
            Role_ID,
            Form_ID,
            Can_View,
            Can_Edit,
            Can_Prepare_By,
            Can_Change_Status_By,
            Prepared_By_ID,
            Prepared_Date
        ) SELECT _Role_ID, _Form_ID, _Can_View, _Can_Edit, _Can_Prepare_By, _Can_Change_Status_By, 0, GETDATE() FROM @Temp
    END
END

Answer 1

The statement in your exists clause is not correctly formed - and that is throwing the error. You have:

IF EXISTS(SELECT * FROM ROLE_RIGHTS WHERE Role_ID = @Temp.Role_ID AND Form_ID = @Temp.Form_ID)

But it should be:

if exists (select * from ROLE_RIGHTS as rr inner join @Temp as tp on rr.Role_ID = tp._Role_ID and rr.Form_ID = tp._Form_ID)

That correction is pointless - as @lad2025 has already mentioned.

Answer 2

To update/insert multiple records at once use MERGE:

ALTER PROCEDURE SP_SETUP_ROLES_RIGHTS_SAVE_AND_UPDATE
(@Temp _ROLERIGHTSSCHEMA ReadOnly)
AS
BEGIN
    MERGE Role_Rights rr
    USING @Temp t
      ON rr.Role_ID = t._Role_ID 
         and rr.Form_ID = t._Form_ID
    WHEN MATCHED THEN
       Update set Can_View = t._Can_View,
                    Can_Edit = t._Can_Edit,
                    Can_Prepare_By = t._Can_Prepare_By,
                    Can_Change_Status_By = _Can_Change_Status_By,
                    Modified_By_ID = 0,
                    Modified_Date = GETDATE()
    WHEN NOT MATCHED BY TARGET THEN
       INSERT ( Role_ID,
            Form_ID,
            Can_View,
            Can_Edit,
            Can_Prepare_By,
            Can_Change_Status_By,
            Prepared_By_ID,
            Prepared_Date)
       VALUES (_Role_ID, _Form_ID, _Can_View, _Can_Edit,
               _Can_Prepare_By, _Can_Change_Status_By, 0, GETDATE() );
END;

EDIT:

MERGE over IF EXISTS/UPDATE/INSERT set based:

Single insert   -- no difference
Single update   -- no difference
Multiple insert -- no difference
Multiple update -- no difference
Multiple insert/update:  MERGE     -- will handle it correctly 
                         IF EXISTS -- you will lose records to insert

---

UPDATE/INSERT without IF part:

Update Role_Rights
        set Can_View = t._Can_View,
            Can_Edit = t._Can_Edit,
            Can_Prepare_By = t._Can_Prepare_By,
            Can_Change_Status_By = _Can_Change_Status_By,
            Modified_By_ID = 0,
            Modified_Date = GETDATE()
            From @Temp t
WHERE Role_Rights.Role_ID = t._Role_ID and Role_Rights.Form_ID = t._Form_ID;

INSERT INTO Role_Rights (
        Role_ID,
        Form_ID,
        Can_View,
        Can_Edit,
        Can_Prepare_By,
        Can_Change_Status_By,
        Prepared_By_ID,
        Prepared_Date
    ) 
 SELECT _Role_ID, _Form_ID, _Can_View, _Can_Edit,
        _Can_Prepare_By, _Can_Change_Status_By, 0, GETDATE() 
 FROM @Temp t
 WHERE NOT EXISTS (SELECT 1
                   FROM Role_Rights rr
                   WHERE rr.Role_ID = t._Role_ID 
                     and rr.Form_ID = t._Form_ID);

Answer 3

Try with multiple IF clauses within statement after ELSE, check if this works

CREATE TYPE _ROLERIGHTSSCHEMA as Table (
        _Role_ID int,
        _Form_ID int,
        _Form_Name varchar(100),
        _Can_View bit,
        _Can_Edit bit,
        _Can_Prepare_By bit,
        _Can_Change_Status_By bit,
        _Prepared_By_ID int,
        _Prepared_Date datetime
    )


    ALTER PROCEDURE SP_SETUP_ROLES_RIGHTS_SAVE_AND_UPDATE
    (@Temp _ROLERIGHTSSCHEMA ReadOnly)
    AS
    BEGIN
        IF EXISTS(SELECT * FROM ROLE_RIGHTS WHERE Role_ID = @Temp.Role_ID AND Form_ID = @Temp.Form_ID)
        BEGIN
            Update Role_Rights
            set Can_View = t._Can_View,
                Can_Edit = t._Can_Edit,
                Can_Prepare_By = t._Can_Prepare_By,
                Can_Change_Status_By = _Can_Change_Status_By,
                Modified_By_ID = 0,
                Modified_Date = GETDATE()
                From @Temp t
                WHERE Role_Rights.Role_ID = t._Role_ID and Role_Rights.Form_ID = t._Form_ID
            END
        ELSE
        BEGIN
        IF NOT EXISTS(SELECT * FROM ROLE_RIGHTS WHERE Role_ID = @Temp.Role_ID AND Form_ID = @Temp.Form_ID)
        BEGIN 
            INSERT INTO Role_Rights (
                Role_ID,
                Form_ID,
                Can_View,
                Can_Edit,
                Can_Prepare_By,
                Can_Change_Status_By,
                Prepared_By_ID,
                Prepared_Date
            ) SELECT _Role_ID, _Form_ID, _Can_View, _Can_Edit, _Can_Prepare_By, _Can_Change_Status_By, 0, GETDATE() FROM @Temp
        END
        END 
    END