Hi, I have a patient table, nurse table and a doctor table but would like to store the common details, e.g address, phone no etc in a details table rather than have them in the seperate tables.
I have written a stored procedure that I would like to use to insert data into the details table and will call this procedure from within my application straight after calling the one I have written to populate the patient table, I am trying to extract the last inserted ID from the patient table which will be the foreign key in my detail table, below is the code I am trying as well as the error code I am getting!
CREATE Procedure addDocDetail
@detailID int = [SELECT IDENT_CURRENT('Doctor')],
INSERT INTO Detail(DetailID,Surname,FirstName,Address1,Address2,Town,County,PostCode,PhoneNo,Email,MobileNo,DateOfBirth,Gender)
VALUES(CAST(@detailID AS INT),@surname,@firstname,@address1,@address2,@town,@county,@postcode,@phoneno,@email,@mobileno,@dob,@gender)
exec addDocDetail @surname ='MacNamara',@firstname='George',@address1='12 Porter Flats',@address2='Fairview Street',@town='Bexleyheath',@county='Kent',@postcode='BX6 7UT',@phoneno='01469885446',@firstname.lastname@example.org',@mobileno='07798655589',@dob='12/8/1973',@gender='M'
Msg 245, Level 16, State 1, Procedure addDocDetail, Line 0
Conversion failed when converting the nvarchar value 'SELECT IDENT_CURRENT('Doctor')' to data type int.