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

Open in new window

VBBRettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>SET @MemberFamilyID = SCOPE_IDENTITY();

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
0
VBBRettAuthor Commented:
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.
0
chapmandewCommented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Aneesh RetnakaranDatabase AdministratorCommented:
OPTION 1

ALTER PROCEDURE [dbo].[InsertMemberFamilyInfo]
      -- 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




0
VBBRettAuthor Commented:
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)

0
Aneesh RetnakaranDatabase AdministratorCommented:
did u try my post , i assume that "MemberFamilyID "  is the identity column  
0
VBBRettAuthor Commented:
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?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what field IS the identity field?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
VBBRettAuthor Commented:
The identity field is MemberFamilyId
0
Geoff BryanDeveloperCommented:
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.
0
VBBRettAuthor Commented:
EmployeeID is a foreign key.  memberfamilyid is a primary master key in this table that I am trying to insert into
0
VBBRettAuthor Commented:
As you can see here in the following picture, memberfamilyid is the identity:


isidentity.jpg
0
Geoff BryanDeveloperCommented:
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'?
0
VBBRettAuthor Commented:
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
0
VBBRettAuthor Commented:
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?
0
Geoff BryanDeveloperCommented:
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.
0
chapmandewCommented:
I was able to answer this, right?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
chapmandewCommented:
I don't believe all the params were being passed in...
0
wiljeCommented:
Can you show us the code you are using to call this procedure?  I would expect the code to be something like the attached if called from SQL Server.
You would replace where I have {...} with actual values.  For example, you would replace {created date} with '20090714'.
One of the other issues you have in your procedures parameters is how you are declaring the variables.  If you do not specify the length for char/varchar/nchar/nvarchar parameters it will default to 1.
I would also reconsider how you are specifying your list of allergies.  You really should not be setting that up as a list, and should create a separate table related to the table and use a junction table to build the one to many relationship.  The junction table would only need two columns - the MemberFamilyID and the ID from the Allergies table.

Declare @MemberFamilyID int;
 
Execute dbo.InsertMemberFamilyInfo
        @MemberFamilyID      = @MemberFamilyID output
        ,@EmployeeID         = {employee id}
        ,@MemberFamilyNumber = {member family number}
        ,@Allergies          = (list of allergies?
        ,@Weight             = {member weight}
        ,@Age                = {member age}
        ,@DateOfBirth        = {date of birth} 
        ,@MemberFamilyCreatedDate = {created date}
        ,@MemberFamilyCreatedBy   = {created by};
 
Select MemberFamilyID;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.