Solved

Primary Keys and Stored Procedures

Posted on 2009-07-13
19
246 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
[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
  • 10
  • 9
19 Comments
 
LVL 22

Expert Comment

by:dportas
ID: 24843536
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
ID: 24843649
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
ID: 24843741
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:VBBRett
ID: 24850289
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
ID: 24850625
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
ID: 24850743
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
ID: 24850840
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
ID: 24850967
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
ID: 24851035
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
 

Author Comment

by:VBBRett
ID: 24851119
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
ID: 24851168
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
ID: 24851270
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
ID: 24851333
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
ID: 24851702
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
ID: 24851792
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
ID: 24851943
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
ID: 24852983
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
ID: 24853349
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
ID: 24853503
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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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