Link to home
Start Free TrialLog in
Avatar of VBBRett
VBBRett

asked on

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!
Avatar of dportas
dportas

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;
Avatar of VBBRett

ASKER

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
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!
Avatar of VBBRett

ASKER

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;
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.
Avatar of VBBRett

ASKER

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.
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.
Avatar of VBBRett

ASKER

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

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' ;
Avatar of VBBRett

ASKER

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.
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.
Avatar of VBBRett

ASKER

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)
Avatar of VBBRett

ASKER

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.  
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' ;
Avatar of VBBRett

ASKER

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

Avatar of VBBRett

ASKER

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

Avatar of VBBRett

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of dportas
dportas

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial