Solved

Insert Primary Keys with Stored Procedures

Posted on 2009-07-14
21
422 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:VBBRett
  • 8
  • 4
  • 3
  • +3
21 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24850362
>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
 

Author Comment

by:VBBRett
ID: 24850457
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24850514
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24850520
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
 

Author Comment

by:VBBRett
ID: 24850649
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24850822
did u try my post , i assume that "MemberFamilyID "  is the identity column  
0
 

Author Comment

by:VBBRett
ID: 24850870
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24851064
what field IS the identity field?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24851082
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
 

Author Comment

by:VBBRett
ID: 24851099
The identity field is MemberFamilyId
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 4

Expert Comment

by:vbgb
ID: 24851330
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
 

Author Comment

by:VBBRett
ID: 24851393
EmployeeID is a foreign key.  memberfamilyid is a primary master key in this table that I am trying to insert into
0
 

Author Comment

by:VBBRett
ID: 24851428
As you can see here in the following picture, memberfamilyid is the identity:


isidentity.jpg
0
 
LVL 4

Expert Comment

by:vbgb
ID: 24851589
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
 

Author Comment

by:VBBRett
ID: 24851683
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
 

Author Comment

by:VBBRett
ID: 24851981
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
 
LVL 4

Expert Comment

by:vbgb
ID: 24853426
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24853440
I was able to answer this, right?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24853455
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24853462
I don't believe all the params were being passed in...
0
 
LVL 7

Accepted Solution

by:
wilje earned 500 total points
ID: 24855856
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now