Solved

Insert Primary Keys with Stored Procedures

Posted on 2009-07-14
21
428 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +3
21 Comments
 
LVL 143

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 143

Expert Comment

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

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
 
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 143

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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