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?
CodePudding user response:
INSERT INTO dbo.customer (@customer_id);
in your first IF
block is not a proper SQL statement. Instead:
INSERT INTO dbo.customer (customer_id) VALUES (@customer_id);
The same issue for your second INSERT
statement as well. The format here is:
INSERT INTO dbo.item (serial_num, customer_id)
VALUES (@serial_num, @customer_id);
CodePudding user response:
It looks like you have the wrong WHERE
clause
SELECT @customer_id = customer_id
FROM dbo.customer
WHERE address = @customer_address -- not @customer_id