Stored procedure in SQL Server with select query then if condition and then insert query

Question

I want to create a stored procedure in which first select statement and depending on the selected parameter if valid, insert record in the another table else do nothing. How to write the stored procedure for this?

I have tried with stored procedure and it is executed with no errors, but when I tried to EXEC stored procedure, it doesn't do the task as written in procedure.

CREATE PROCEDURE sp_CreateExpiryDocumentFollowup
    (@param INT = NULL,
     @param2 INT = NULL,
     @param3 INT = 1,
     @param4 BIT = 0,
     @followupid INT = NULL)
AS 
BEGIN
    SELECT 
        @param1 = [TABLE_A].[VEHICLE_ID],
        @param2 = [TABLE_A].[VEHICLE_DOCUMENT_ID],
        @followupid = [TABLE_B].[FOLLOWUP_ID]
    FROM 
        [TABLE_A]
    LEFT JOIN 
        [TABLE_B] ON [TABLE_B].[VEHICLE_DOCUMENT_ID] != [TABLE_A].[VEHICLE_DOCUMENT_ID]
    WHERE 
        [TABLE_A].[STATUS] = 1;

    IF @followupid = NULL
    BEGIN
        INSERT INTO [TABLE_B] (VALUE_1, VALUE_2, VALUE_3, VALUE_4)
        VALUES (@param1, @param2, @param3, @param4)
    END
END
GO

I expect the record to insert in the TABLE_B if @followupid is null. The @followupid is null as I executed the select statement only. But while executing whole stored procedure it will return the result 0 row which in my case should be 1. And I checked the table as well, no any record is inserted but stored procedure runs successfully.

Answer 1

Here's how I would write it:

CREATE PROCEDURE sp_CreateExpiryDocumentFollowup
(
    --@param1 int = null -- this is no longer needed
    --@param2 int = null -- this is no longer needed
    @param3 int = 1,
    @param4 bit = 0
    --,@followupid int = null -- -- this is no longer needed
)
AS BEGIN

    INSERT INTO [TABLE_B] (VALUE_1, VALUE_2, VALUE_3, VALUE_4)
    SELECT [TABLE_A].[VEHICLE_ID], [TABLE_A].[VEHICLE_DOCUMENT_ID], @param3, @param4
    FROM [TABLE_A]
    LEFT JOIN [TABLE_B] 
        -- as stated in the comments to the questions, 
        -- Shouldn't this condition be `=` instead of `!=`?
        ON [TABLE_B].[VEHICLE_DOCUMENT_ID] != [TABLE_A].[VEHICLE_DOCUMENT_ID]
    WHERE [TABLE_A].[STATUS] = 1
    AND [TABLE_B].[FOLLOWUP_ID] IS NULL

END
GO

Instead of first selecting and then inserting, you can do an insert...select operation which leads to a shorter, more readable code. This way, if the select statement doesn't return any rows, nothing gets inserted into the target table.

Also, you can't use equality operators on NULL in SQL. Instead, you can only use IS NULL or IS NOT NULL (Or NOT IS NULL if you like that better).

Please note that if the select statement returns more than one row, all of them will be inserted into the target table.