T-SQL stored procedure: update local variable inside IF/ELSE statement

I have an item table that has a foreign key constraint customer_id that references a customer table. The customer_id column is of type uniqueidentifier which needs to be generated on insert (there is no value we could use for this provided by the client). I am created a T-SQL (using SQL Server Express) stored procedure to be called from C# using ADO Entity Data Model to add records to the database.

The objective is to create an item (if it doesn’t already exist) with a reference to an already existing customer record or a newly created customer record by using the declared @customer_id variable.

Below is a simplified version of my stored procedure.

CREATE PROCEDURE dbo.uspInsertRecord
    @serial_num AS INT,
    @customer_address AS VARCHAR(30)
AS 
    DECLARE @customer_id AS UNIQUEIDENTIFIER;

    IF NOT EXISTS (SELECT 1 FROM dbo.customer WHERE address = @customer_address)
    BEGIN 
        SET @customer_id = NEWID()
 
        INSERT INTO dbo.customer (customer_id, customer_address)
        VALUES (@customer_id, @customer_address)
    END
    ELSE
    BEGIN
        SELECT @customer_id = customer_id 
        FROM dbo.customer
        WHERE customer_id = @customer_id
    END

    IF NOT EXISTS (SELECT 1 FROM dbo.item WHERE serial_num = @serial_num)
   BEGIN
   INSERT INTO dbo.item (serial_num, customer_id)
   VALUES (@serial_num, @customer_id)
   END

This procedure works for newly created customer records, but not when referencing an existing customer record.

Attempting to add items with an existing associated customer_id throws a

SqlException: Cannot insert the value NULL into column ‘customer_id’, table ‘dbo.item’

I am fairly new to T-SQL, but this seems to be a scope issue. Any thoughts?

Answer

It looks like you have the wrong WHERE clause

        SELECT @customer_id = customer_id 
        FROM dbo.customer
        WHERE address = @customer_address    -- not @customer_id

Advertisement

Answer