May 19, 2008 at 1:40 pm
Hi I am having an issue with a stored procedure that declares 2 variables and i am trying to use @@IDENTITY for the primary key which is an autoincrement, after an inset...here is the stored procedure and error..
ALTER PROCEDURE Create$Employees
(
@DepartmentID int,
@FirstName varchar(50),
@LastName varchar(50),
@EmployeeNumber varchar(50),
@HireDate datetime,
@SinNum varchar(50),
@BirthDate datetime,
@HomePhone varchar(50),
@CellPhone varchar(50),
@EmailAddress varchar(50),
@Salaried char(1),
@PayRate money,
@Salary money,
@ProvinceID int,
@CountryID int,
@Address1 varchar(50),
@Address2 varchar(50),
@City varchar(50),
@Postal varchar(50)
)
AS
declare @AddID int
declare @EmpID int
insert into [Addresses]
(Address1, Address2, City, Postal, ProvinceID, CountryID)
values
(@Address1, @Address2, @City, @Postal, @ProvinceID, @CountryID)
set @AddID = @@IDENTITY
insert into [Employees]
(DepartmentID, AddressID, FirstName, LastName, EmployeeNumber, HireDate, SinNum, Birthdate, HomePhone, CellPhone, EmailAddress)
values
(@DepartmentID, @AddID, @FirstName, @LastName, @EmployeeNumber, @HireDate, @SinNum, @Birthdate, @HomePhone, @CellPhone, @EmailAddress)
set @EmpID = @@IDENTITY
insert into [PayScale]
(Salaried, PayRate, Salary, EmployeeID)
values
(@Salaried, @PayRate, @Salary, @EmpID)
RETURN
here is the error...
Cannot insert the value NULL into column 'EmployeeID', table 'WS1.dbo.Employees'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PayScale_Employees". The conflict occurred in database "WS1", table "dbo.Employees", column 'EmployeeID'.
The statement has been terminated.
(1 row(s) affected)
(0 row(s) returned)
@RETURN_VALUE = -6
Finished running [dbo].[Create$Employees].
I am not sure what i am doing wrong.
Thanks for the help
Chris
May 19, 2008 at 1:44 pm
May 19, 2008 at 1:50 pm
Chris - it looks like EmployeeID isn't an identity (autoincrementing) column, so trying to insert into your employee table without an actual value for employeeID will fail.
By the way - you should avoid @@identity like the plague, since it can pick up incorrect values depending on varius situations... Use scope_ident or ident_current('tablename') instead.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 19, 2008 at 1:58 pm
John is on the correct path. If a table does not have an identity column, @@identity will contain null after the insert. Also, I suspect you have a logic problem
instead of
insert into [Addresses]
(Address1, Address2, City, Postal, ProvinceID, CountryID)
values
(@Address1, @Address2, @City, @Postal, @ProvinceID, @CountryID)
set @AddID = @@IDENTITY
insert into [Employees]
(DepartmentID, AddressID, FirstName, LastName, EmployeeNumber, HireDate, SinNum, Birthdate, HomePhone, CellPhone, EmailAddress)
values
(@DepartmentID, @AddID, @FirstName, @LastName, @EmployeeNumber, @HireDate, @SinNum, @Birthdate, @HomePhone, @CellPhone, @EmailAddress)
set @EmpID = @@IDENTITY
use the local variable like this
insert into [Addresses]
(Address1, Address2, City, Postal, ProvinceID, CountryID)
values
(@Address1, @Address2, @City, @Postal, @ProvinceID, @CountryID)
set @AddID = @@IDENTITY
insert into [Employees]
(DepartmentID, AddressID, FirstName, LastName, EmployeeNumber, HireDate, SinNum, Birthdate, HomePhone, CellPhone, EmailAddress)
values
(@DepartmentID, @AddID, @FirstName, @LastName, @EmployeeNumber, @HireDate, @SinNum, @Birthdate, @HomePhone, @CellPhone, @EmailAddress)
set @EmpID = [highlight]@AddID[/highlight]
May 19, 2008 at 2:57 pm
Hi Matt,
After reading your reply, i looked over my structure again and noticed that I never did set my EmployeeID to an autoincrement. One I did that my stored procedure ran without a hitch.
I am going to look into the ident_current('tablename') and scope_identity.
thanks for all your help.
Chris
May 19, 2008 at 3:01 pm
Hi SqlServerLifer,
I think I understand what you are talking about. It looks as though it does the same thing as I was attempting. From what i see the difference is that your way is making absolutly sure that the EmpID, contains the correct ID from the AddID which in the end would be the right ID that would go into the Employees table.
Thanks for the reply and the help as well
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply