VBBRett
asked on
Insert Primary Keys with Stored Procedures
I need a simple stored procedure that does an insert. The problem is, every person that I have asked gives me this complex answer that is just not noteworthy. Please take this stored procedure that I am giving you and make it so that memberfamilyid, which is the primary key, can be inserted and increment the primary key to the next value. I am not asking for something complex, this is something very simple and I expect the answer to be simple. Thank you! Please see the stored procedure below:
USE [EmployeeScreening]
GO
/****** Object: StoredProcedure [dbo].[InsertMemberFamilyInfo] Script Date: 07/14/2009 10:53:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Brett Smith>
-- Create date: <07/13/09>
-- Description: <Insert Employee Family Information>
-- =============================================
ALTER PROCEDURE [dbo].[InsertMemberFamilyInfo]
-- Add the parameters for the stored procedure here
@MemberFamilyID int OUTPUT,
@EmployeeID int,
@MemberFamilyNumber int,
@Allergies nvarchar,
@Weight int,
@Age int,
@DateOfBirth datetime,
@MemberFamilyCreatedDate datetime,
@MemberFamilyCreatedBy nvarchar(50)
AS
BEGIN
-- Insert statements for procedure here
INSERT INTO MemberFamilyInfo (EmployeeID, MemberFamilyNumber, Allergies,
Weight, Age, DateOfBirth, MemberFamilyCreatedDate, MemberFamilyCreatedBy) VALUES
@EmployeeID, @MemberFamilyNumber, @Allergies, @Weight, @Age, @DateOfBirth,
@MemberFamilyCreatedDate, @MemberFamilyCreatedBy);
SET @MemberFamilyID = SCOPE_IDENTITY();
END;
GO
ASKER
How do I do the stored procedure so that I don't even have to put any values for the primary key? This is an int primary key, it should be incremented.
If it is an identity column, you don't need to insrt a value for it...it will increment for you. Just omit the field from your insert statement.
OPTION 1
ALTER PROCEDURE [dbo].[InsertMemberFamilyI
-- Add the parameters for the stored procedure here
@MemberFamilyID int = null OUTPUT,
@EmployeeID int,
@MemberFamilyNumber int,
@Allergies nvarchar,
@Weight int,
@Age int,
@DateOfBirth datetime,
@MemberFamilyCreatedDate datetime,
@MemberFamilyCreatedBy nvarchar(50)
AS
BEGIN
-- Insert statements for procedure here
INSERT INTO MemberFamilyInfo (EmployeeID, MemberFamilyNumber, Allergies,
Weight, Age, DateOfBirth, MemberFamilyCreatedDate, MemberFamilyCreatedBy) VALUES
@EmployeeID, @MemberFamilyNumber, @Allergies, @Weight, @Age, @DateOfBirth,
@MemberFamilyCreatedDate, @MemberFamilyCreatedBy);
SET @MemberFamilyID = SCOPE_IDENTITY();
END;
GO
ALTER PROCEDURE [dbo].[InsertMemberFamilyI
-- Add the parameters for the stored procedure here
@MemberFamilyID int = null OUTPUT,
@EmployeeID int,
@MemberFamilyNumber int,
@Allergies nvarchar,
@Weight int,
@Age int,
@DateOfBirth datetime,
@MemberFamilyCreatedDate datetime,
@MemberFamilyCreatedBy nvarchar(50)
AS
BEGIN
-- Insert statements for procedure here
INSERT INTO MemberFamilyInfo (EmployeeID, MemberFamilyNumber, Allergies,
Weight, Age, DateOfBirth, MemberFamilyCreatedDate, MemberFamilyCreatedBy) VALUES
@EmployeeID, @MemberFamilyNumber, @Allergies, @Weight, @Age, @DateOfBirth,
@MemberFamilyCreatedDate, @MemberFamilyCreatedBy);
SET @MemberFamilyID = SCOPE_IDENTITY();
END;
GO
ASKER
I ran the stored procedure manually and this is the error I get:
Msg 544, Level 16, State 1, Procedure InsertMemberFamilyInfo, Line 21
Cannot insert explicit value for identity column in table 'MemberFamilyInfo' when IDENTITY_INSERT is set to OFF.
(1 row(s) affected)
Msg 544, Level 16, State 1, Procedure InsertMemberFamilyInfo, Line 21
Cannot insert explicit value for identity column in table 'MemberFamilyInfo' when IDENTITY_INSERT is set to OFF.
(1 row(s) affected)
did u try my post , i assume that "MemberFamilyID " is the identity column
ASKER
Yes. I tried it on my web page and no dice and then I tried it manually without filling any values for teh memberfamilyid and still have problems. What am I doing wrong?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
what field IS the identity field?
resp: if MemberFamilyId is not identity, which value should it take?
otherwise, what should the @MemberFamilyId output parameter be for?
furthermore: if MemberFamilyId is not identity, some other field is, you should need another output parameter?
otherwise, what should the @MemberFamilyId output parameter be for?
furthermore: if MemberFamilyId is not identity, some other field is, you should need another output parameter?
ASKER
The identity field is MemberFamilyId
It sounds like the MemberFamilyID field is NOT set as an Identity field in the table design. If it was then the SP would work.
You need to open the table in the designer (in VS or SQL Management Studio) and make sure that this field has True in the Identity property. By default this will create an incrementing identity field and the value returned in your @MemberFamilyID parameter will be the integer value stored in this field in the new record.
Another possibility:
The error message indicates that you are trying to explicitly set the value in an identity column, which suggests that EmployeeID is actually the identity field. If this is the case, you do not need to set the value of EmployeeID in the INSERT statement.
You need to open the table in the designer (in VS or SQL Management Studio) and make sure that this field has True in the Identity property. By default this will create an incrementing identity field and the value returned in your @MemberFamilyID parameter will be the integer value stored in this field in the new record.
Another possibility:
The error message indicates that you are trying to explicitly set the value in an identity column, which suggests that EmployeeID is actually the identity field. If this is the case, you do not need to set the value of EmployeeID in the INSERT statement.
ASKER
EmployeeID is a foreign key. memberfamilyid is a primary master key in this table that I am trying to insert into
ASKER
Just one thought - it is possible to have more than one identity field, even though only one is the primary key. Can you check that no other integer fields are set to 'Is Identity'?
ASKER
EmployeeID is not an identity in the table I am trying to insert. I am so frustrated and I do not know what to do
ASKER
OK, after testing a few things, I seemed to have found the problem. The stored procedure executes and when I try to type values into the text boxes so that the values get passed to the stored procedure variables, the values don't stick. What I mean by that is I get an error like the following:
(1 row(s) affected)
Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@Allergies', which was not supplied.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
But if I assign the values before that and assign the values to the parameters at declaration time, the values stick. What am I to do now?
(1 row(s) affected)
Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@Allergies', which was not supplied.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
But if I assign the values before that and assign the values to the parameters at declaration time, the values stick. What am I to do now?
Assuming that you are executing the SP in SSMS, the error you are getting indicates that you did not enter anything for the @Allergies parameter. You are allowing Nulls in this column so you can either tick 'Pass Null Value' or type a space in the Value column.
I was able to answer this, right?
2 things:
@Allergies nvarchar,
should be:
@Allergies nvarchar(200),
how do you call the procedure?
>Procedure or function 'InsertMemberFamilyInfo' expects parameter '@Allergies', which was not supplied.
definitively indicates you do it wrongly.
@Allergies nvarchar,
should be:
@Allergies nvarchar(200),
how do you call the procedure?
>Procedure or function 'InsertMemberFamilyInfo' expects parameter '@Allergies', which was not supplied.
definitively indicates you do it wrongly.
I don't believe all the params were being passed in...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so, the field MemberFamilyID is marked as identity?
yes => the code works as is
no => then:
? either change the field to be identity
? or we will need to change the code indeed to a less-optimal solution.
please clarify