Solved

Primary Keys and Stored Procedures

Posted on 2009-07-13
19
206 Views
Last Modified: 2012-05-07
I am trying to do a stored procedure with an identity primary key that inserts when a new value is inserted into the table manually.  The problem I am getting is I have an error when I try to pull this off in a stored procedure.  How do I do an insert stored procedure into a table without getting the primary key error and how do I get the primary key value to increment when I am doing an insert?  Thank you!
0
Comment
Question by:VBBRett
  • 10
  • 9
19 Comments
 
LVL 22

Expert Comment

by:dportas
Comment Utility
Here's an example. If you need more help then please tell us what error message you are getting and post the code that causes it.

CREATE TABLE tbl (i INT IDENTITY PRIMARY KEY, x VARCHAR(10) NOT NULL UNIQUE);

GO

CREATE PROC tblInsert
(@x VARCHAR(10),
 @i INT OUTPUT)
AS
BEGIN;
 INSERT INTO tbl (x) VALUES (@x);
 SET @i = SCOPE_IDENTITY();
END;
GO

DECLARE @id INT;
EXEC tblInsert @x = 'foo', @i = @id OUT;

SELECT @id Id;
0
 

Author Comment

by:VBBRett
Comment Utility
I don't need a create table stored procedure, I need an insert table stored procedure.  Please take a look at the stored procedure I am trying to critique so that when it is done, I have no problem inserting data into my table.  Right now I am having problems inserting data into my table because of the primary key.

USE [EmployeeScreening]
GO
/****** Object:  StoredProcedure [dbo].[InsertMemberFamilyInfo]    Script Date: 07/13/2009 15:26:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <user>
-- Create date: <07/13/09>
-- Description:      <Insert Employee Family Information>
-- =============================================
ALTER PROCEDURE [dbo].[InsertMemberFamilyInfo]
      -- Add the parameters for the stored procedure here      
      @MemberFamilyID                        int,
      @EmployeeID                              int,
      @MemberFamilyNumber                  int,
      @Allergies                              nvarchar,
      @Weight                                    int,
      @Age                                    int,
      @DateOfBirth                        datetime,
      @MemberFamilyCreatedDate      datetime,
      @MemberFamilyCreatedBy            nvarchar(50),
      @maxfamilyid                        int,

AS BEGIN

          -- Insert statements for procedure here
      INSERT INTO MemberFamilyInfo (MemberFamilyId, EmployeeID, MemberFamilyNumber, Allergies,
      Weight, Age, DateOfBirth, MemberFamilyCreatedDate, MemberFamilyCreatedBy) VALUES
(@MemberFamilyID, @EmployeeID, @MemberFamilyNumber, @Allergies, @Weight, @Age, @DateOfBirth,
@MemberFamilyCreatedDate, @MemberFamilyCreatedBy)
            
      END
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
I posted an example INSERT stored procedure. Please look again at my first answer. You'll see it has an example of what you say you are trying to do.

Your code fragment tells me nothing useful I'm afraid. It's incomplete / has a syntax error because of an extra comma before AS. Apart from that, you still haven't explained what's wrong with it. "having problems" isn't much to go on!
0
 

Author Comment

by:VBBRett
Comment Utility
OK, here is my insert stored procedure that I am trying to work with.  Please tell me what I am doing wrong and what it should be.  Using your concepts, i am still running into problems:

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:            <user>
-- 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

DECLARE @ID INT;
EXEC @MemberFamilyID = @ID OUT;

SELECT @ID ID;
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
This line: EXEC @MemberFamilyID = @ID OUT;
Should read: EXEC dbo.InsertMemberFamilyInfo @MemberFamilyID = @ID OUT;
Also, you missed an open bracket after the keyword VALUES.
It seems I didn't make myself clear enough in my previous two responses so I'll say it again: If you need more help then please tell us what error message(s) you are experiencing. "Running into problems" is not going to help other people to help you.
0
 

Author Comment

by:VBBRett
Comment Utility
I get this error when I used your solution:

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.
Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@EmployeeID', which was not supplied.
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
You need to supply ALL of the required parameters to the procedure in the EXEC statement. If you want some parameters to be optional (nullable in the table for example) then put the default values in the procedure declaration. Like this:

ALTER PROCEDURE [dbo].[InsertMemberFamilyInfo]
 ...
      @EmployeeID                              int = NULL,
      @MemberFamilyNumber                  int = 123 /* Default value */,
 ...

In your INSERT statement, do not include the IDENTITY column, either in the INSERT column list or the values clause. The IDENTITY is assigned automatically. The first error message is telling you that you are trying to insert a value to it, which is not allowed. Check which column is the IDENTITY one if you aren't sure.
0
 

Author Comment

by:VBBRett
Comment Utility
OK, here is the error that I received


(1 row(s) affected)
Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@EmployeeID', which was not supplied.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

below is the query
USE [EmployeeScreening]

GO

/****** Object:  StoredProcedure [dbo].[InsertMemberFamilyInfo]    Script Date: 07/14/2009 11:50:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		<user>

-- Create date: <07/13/09>

-- Description:	<Insert Employee Family Information>

-- =============================================

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;
 

DECLARE @ID INT;

EXEC dbo.InsertMemberFamilyInfo @MemberFamilyID = @ID OUT
 

SELECT @ID ID;

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
You didn't specify EmployeeID and the other values.

EXEC dbo.InsertMemberFamilyInfo
@MemberFamilyID = @ID OUT,
@EmployeeID = 1,
@MemberFamilyNumber = 1,
@Allergies = '',
@Weight = 1,
@Age = 99,
@DateOfBirth = '19000101',
@MemberFamilyCreatedDate = '20090714',
@MemberFamilyCreatedBy = 'ME' ;
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:VBBRett
Comment Utility
When I ran the stored procedure manually, I entered the values in the little text boxes that were prompted from SQL Server.  I also tried to run the stored procedure in my web app and nothing was entered.
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
I suggest you debug this through Management Studio using an EXEC statement until it is working. Did you try my last suggestion? The error message should tell you clearly which parameter(s) you haven't supplied if that's the problem.
0
 

Author Comment

by:VBBRett
Comment Utility
This is the error I got:


(1 row(s) affected)
Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@EmployeeID', which was not supplied.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
0
 

Author Comment

by:VBBRett
Comment Utility
So why would I get an error based on one row?  EmployeeID is the second row of information being inserted, however, it is not going through.  
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
The message is telling you that you didn't supply the @EmployeeID parameter. You must have done something different to what I posted because I included it in my EXEC statement:

EXEC dbo.InsertMemberFamilyInfo
@MemberFamilyID = @ID OUT,
@EmployeeID = 1,
@MemberFamilyNumber = 1,
@Allergies = '',
@Weight = 1,
@Age = 99,
@DateOfBirth = '19000101',
@MemberFamilyCreatedDate = '20090714',
@MemberFamilyCreatedBy = 'ME' ;
0
 

Author Comment

by:VBBRett
Comment Utility
HAHA!  Take a look at my stored procedure, you can clearly see that the EmployeeID parameter is there.  SQL Server is one mucked up piece of crap that can't even execute one insert?  You gotta be kidding me!


USE [EmployeeScreening]

GO

/****** Object:  StoredProcedure [dbo].[InsertMemberFamilyInfo]    Script Date: 07/14/2009 13:09:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		<user>

-- Create date: <07/13/09>

-- Description:	<Insert Employee Family Information>

-- =============================================

ALTER PROCEDURE [dbo].[InsertMemberFamilyInfo]

	-- Add the parameters for the stored procedure here	

	@MemberFamilyID				int = NULL OUTPUT,

	@EmployeeID					int,

	@MemberFamilyNumber			int,

	@Allergies					nvarchar(50),

	@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;
 

DECLARE @ID INT;

EXEC dbo.InsertMemberFamilyInfo @MemberFamilyID = @ID OUT
 

SELECT @ID ID;

Open in new window

0
 

Author Comment

by:VBBRett
Comment Utility
It appears that no matter what values I place into the boxes, the stored procedure is not taking the values.  If I assign the variables a value before I run the stored procedure, then it seems to be fine.  What am I to do now?  Is my SQL Server broken?  It accepted values if the values were assigned during the variable declaration, but if I try to type in a value in the text box of the stored procedure module after running the stored procedure, it seems to not want to take those values.  What am I to do?
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
The problem is not with your procedure. It's with your EXEC statement, which you still haven't corrected despite me posting it here twice for you.

I think you need more help than I can give you. If you haven't written and debugged T-SQL code before then I suggest you take a course or study a good book, of which there are plenty out there. Sorry I couldn't help.

0
 

Author Comment

by:VBBRett
Comment Utility
OK, so it appears to work now but i am still getting some kind of error message:  Below is my stored procedure and my error message:


(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 217, Level 16, State 1, Procedure InsertMemberFamilyInfo, Line 31
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

USE [EmployeeScreening]

GO

/****** Object:  StoredProcedure [dbo].[InsertMemberFamilyInfo]    Script Date: 07/14/2009 15:45: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 = NULL OUTPUT,

	@EmployeeID					int,

	@MemberFamilyNumber			int,

	@Allergies					nvarchar(50),

	@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;
 

DECLARE @ID INT;

EXEC dbo.InsertMemberFamilyInfo @MemberFamilyID = @ID OUT,

@EmployeeID = 1,

@MemberFamilyNumber = 1,

@Allergies='',

@Weight=1,

@Age = 99,

@DateOfBirth='19000101',

@MemberFamilyCreatedDate = '20090714',

@MemberFamilyCreatedBy = 'Me'

	 

	 
 

SELECT @ID ID;

Open in new window

0
 
LVL 22

Accepted Solution

by:
dportas earned 500 total points
Comment Utility
You need to put GO after END; and before the DECLARE otherwise you are creating a loop. The DECLARE and EXEC are not supposed to be part of the procedure.
0

Featured Post

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!

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…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 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

11 Experts available now in Live!

Get 1:1 Help Now