I'm new at SQL in general, so I'm have a few problems with some of my stored procedures I'm designing. My procedure takes in 3 parameters. A location (or table name basically, a varchar), a room number (varchar), and a phone number (char) in the form of 'xxx-xxxx'. Here is the code for the procedure.
ALTER PROCEDURE [dbo].[AddPhoneNumberToRoom] @Location varchar(25), @Room varchar(10), @PhoneNumber char(8) AS BEGIN SET NOCOUNT ON; DECLARE @String varchar(100); SELECT @String = ' UPDATE ' + @Location + ' SET PhoneNumber = ' + @PhoneNumber + ' WHERE Room = ' + @Room; EXEC(@String); END
The error I receive is:
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Apartments_PhoneNumbers". The conflict occurred in database "CMPhone", table "dbo.PhoneNumbers", column 'PhoneNumber'."
The phone number is in the PhoneNumber table, so when I do:
UPDATE Apartments SET PhoneNumber='123-4567' WHERE Room='2'
It works just fine. The
PhoneNumbers table also looks like this, just has the one number in it.
PhoneNumber ----------- 123-4567