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?
Advertisement
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