Nested If Statement with Insert Statements in SQL

Question

So I am writing a stored procedure for a webpage that will pull 3 parameters from the webpage and then store one based on the values of the other 2.

ALTER  PROCEDURE [dbo].[PMRAssignDate]

                @PMRID int,
                @Department varchar(255),
                @AssignDate date
AS
BEGIN
    IF EXISTS(SELECT * FROM [ProductInformation].[dbo].[PMRInformation] WHERE PMRID = @PMRID)

        Begin
        IF @Department='Engineering'
            Begin
            Insert INTO
                    [dbo].[PMRInformation]
                    (EngineeringApprovalDate)
            Values
                    (@AssignDate)
            End

        Else IF (@Department='Operations')
            Begin
            Insert INTO
                    [dbo].[PMRInformation]
                    (OperationsApprovalDate)
            Values
                    (@AssignDate)
                End
        Else IF (@Department='AME')
            Begin
            Insert INTO
                    [dbo].[PMRInformation]
                    (AMEApprovalDate)
            Values
                    (@AssignDate)
            End
        Else IF @Department='Finance'
            Begin
            Insert INTO
                    [dbo].[PMRInformation]
                    (FinanceApprovalDate)
            Values
                    (@AssignDate)
                    End
        Else IF @Department='Marketing'
        Begin
            Insert INTO
                    [dbo].[PMRInformation]
                    (MarketingApprovalDate)
            Values
                    (@AssignDate)
        End
        Else IF @Department='Tester'
            Begin
            Insert INTO 
                    [dbo].[PMRInformation]
                    (EngineeringApprovalDate, MarketingApprovalDate, AMEApprovalDate, FinanceApprovalDate, OperationsApprovalDate)
            Values
                    (@AssignDate,@AssignDate,@AssignDate,@AssignDate,@AssignDate)
    End     

    End End

So this stored procedure must find the row where the PMRID equals the sent @PMRID from the webpage. Then it must use the @Department variable to determine which department to store the approval date @AssignDate into. So far the procedure runs but it is not storing the date in the correct row. It will create a new row and insert the approval date into that new row. Could you please help. Thanks!

Answer 1

From your description, it sounds like you would rather UPDATE a row than INSERT a row.

Try changing your inserts to this:

UPDATE dob.PMRInformation
SET = @AssignDate
WHERE PMRID = @PMRID

Answer 2

It is not at all clear what you are trying to do here but all your IF ELSE IF THEN logic can be greatly simplified to a single insert statement.

Insert INTO [dbo].[PMRInformation] (EngineeringApprovalDate, OperationsApprovalDate, AMEApprovalDate, FinanceApprovalDate, MarketingApprovalDate)
select 
    case @Department
        when 'Engineering' then @AssignDate
        when 'Tester' then @AssignDate
        else NULL
    end , 
    case @Department
        when 'Operations' then @AssignDate
        when 'Tester' then @AssignDate
        else NULL
    end , 
    case @Department
        when 'AME' then @AssignDate
        when 'Tester' then @AssignDate
        else NULL
    end , 
    case @Department
        when 'Finance' then @AssignDate
        when 'Tester' then @AssignDate
        else NULL
    end , 
    case @Department
        when 'Marketing' then @AssignDate
        when 'Tester' then @AssignDate
        else NULL
    end