Primary Keys and Stored Procedures

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!
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.

dportasCommented:
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
VBBRettAuthor Commented:
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
dportasCommented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

VBBRettAuthor Commented:
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
dportasCommented:
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
VBBRettAuthor Commented:
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
dportasCommented:
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
VBBRettAuthor Commented:
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
dportasCommented:
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
VBBRettAuthor Commented:
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
dportasCommented:
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
VBBRettAuthor Commented:
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
VBBRettAuthor Commented:
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
dportasCommented:
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
VBBRettAuthor Commented:
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
VBBRettAuthor Commented:
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
dportasCommented:
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
VBBRettAuthor Commented:
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
dportasCommented:
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

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.