CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Ankit Sharma
Ankit Sharma

Reputation: 27

How to Insert a value to table using stored procedure which is having foreign key as one of the column

Created the table using this

CREATE TABLE Appointment
(
    AppointmentNumber INT PRIMARY KEY NOT NULL,
    PatientID INT NOT NULL REFERENCES Patient(PatientID),
    AppointmentDate DATETIME NOT NULL,
    ReminderRequired BIT NOT NULL DEFAULT(0),
    ReasonForVisit VARCHAR(150)
)
GO

Created stored procedure using

CREATE PROCEDURE AddAppointment
    (
    @PatID INT,
    @AppDate DATETIME,
    @RemRequired BIT,
    @ReaForVisit VARCHAR(150)
    )
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET IDENTITY_INSERT Appointment ON

INSERT INTO dbo.Appointment
           ([PatientID],[AppointmentDate],[ReminderRequired],[ReasonForVisit])
     VALUES
           (@PatID,@AppDate,@RemRequired,@ReaForVisit)
SET IDENTITY_INSERT Appointment ON
COMMIT TRANSACTION
END TRY
BEGIN CATCH
        ROLLBACK TRANSACTION
END CATCH
END 
GO

Executing Stored Procedure using

EXECUTE AddAppointment
    @PatID='2',
    @AppDate='1999-09-09',
    @RemRequired=1,
    @ReaForVisit='hahahahaha'
GO

The result is: Command Completed successfully

But there is no new entry in the table when I run

SELECT * FROM Appointment

PLEASE HELP

Upvotes: 0

Views: 67

Answers (2)

Thom A
Thom A

Reputation: 95564

The reason the "Command Completed successfully" is because it did. In the event of an error, you've told SQL Server to ROLLBACK the transaction, and then move on; don't generate an error, don't tell the user about said error, do not pass GO, do not collect £200. THROW the error in the TRY...CATCH and it'll tell you what the error is:

...
END TRY
BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
END CATCH;

As Gordon has then mentioned in their answer, This will tell you that you're trying to either set IDENTITY_INSERT to ON on a table that doesn't have an IDENTITY, or you're trying to INSERT a NULL value into a column that doesn't allow them.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

AppointmentNumber is declared to be NOT NULL, but you haven't provided a value. Hence, the INSERT fails. If you showed the error in the CATCH block, this would probably be obvious.

Presumably, you want it assigned automatically, so make it an identity column:

AppointmentNumber INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,

Also, there should be no reason to set IDENTITY_INSERT to ON in your stored procedure. That should be the default value.

You might have a secondary problem if PatientId does not properly refer to a valid patient.

Upvotes: 2

Related Questions