Solved

How to do an insert or update stored procedure with a primary key?

Posted on 2009-07-13
21
200 Views
Last Modified: 2012-05-07
I am looking for a way to do an insert or update stored procedure with a primary key.  How would I do this?  Here are the fields that I want to insert or update so please tell me how I am to insert or update with a primary key:

MemberFamilyId           int - 'primary key'
EmployeeId                  int
MemberFamilyNumber int
Allergies                      nvarchar(50)
Weight                         int
Age                              int
DateOfbirth                   datetime
MemberFamilyCreatedDate  datetime
MemberFamilyCreatedBy  nvarchar(50)
0
Comment
Question by:VBBRett
  • 10
  • 7
  • 2
  • +1
21 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24841086
Update urTableName
SET ColumnName = 'NewValue'
WHERE MemberFamilyID  =  valueHere
0
 

Author Comment

by:VBBRett
ID: 24841209
But the MemberFamilyID is a incrementing primary key.  How do I make it work when I am doing an insert?
0
 

Author Comment

by:VBBRett
ID: 24841265
I need this to be done as a stored procedure please.
0
 
LVL 15

Expert Comment

by:faiga16
ID: 24841268
It doensn't matter. aneeshattingal suggestion is the way to do it. When you insert another record, it will just increment the ID of MemberFamilyID. Or we don't just understand what you where trying to achieve :)
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24841277
If your doing an INSERT statement and your primary key column is an identity column then you do not have to explicitly provide a value to the primary key. A new value will be added when ever you insert a record. Something like this:

INSERT INTO YourTable (EmployeeId, MemberFamilyNumber, Allergies,Weight,Age,DateOfbirth,MemberFamilyCreatedDate,MemberFamilyCreatedBy)
VALUES (Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8)
If it is not then you will have to explicity enter the primary key value:

INSERT INTO YourTable (MemberFamilyID, EmployeeId, MemberFamilyNumber, Allergies,Weight,Age,DateOfbirth,MemberFamilyCreatedDate,MemberFamilyCreatedBy)
VALUES (Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9)
Aneesh already stated the syntax for your UPDATE statement.
P.
0
 
LVL 15

Expert Comment

by:faiga16
ID: 24841340
CREATE PROCEDURE InsertFamilyMember
@EmployeeId int
,@MemberFamilyNumber int
,@Allergies nvarchar(50)
,@Weight int
,@Age int
,@DateOfbirth datetime
,@MemberFamilyCreatedDate datetime
,@MemberFamilyCreatedBy nvarchar(50)
AS

INSERT INTO YourTable
VALUES (@EmployeeId, @MemberFamilyNumber, @Allergies, @Weight, @Age, @DateOfbirth, @MemberFamilyCreatedDate,@MemberFamilyCreatedBy)





CREATE PROCEDURE UpdateFamilyMember
,@MemberFamilyId int
,@EmployeeId int
,@MemberFamilyNumber int
,@Allergies nvarchar(50)
,@Weight int
,@Age int
,@DateOfbirth datetime
,@MemberFamilyCreatedDate datetime
,@MemberFamilyCreatedBy nvarchar(50)
AS

UPDATE YourTable
SET EmployeeId = @EmployeeId
      ,MemberFamilyNumber = @MemberFamilyNumber
      ,Allergies = @Allergies
      ,Weight = @Weight
      ,Age = @Age
      ,DateOfbirth = @DateOfbirth
      ,MemberFamilyCreatedDate = @MemberFamilyCreatedDate
      ,MemberFamilyCreatedBy = @MemberFamilyCreatedBy
WHERE MemberFamilyId = @MemberFamilyId


0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24841390
You can accompalish this in one stored procedure as well:
CREATE PROCEDURE spUpdateMyTable(

	@MemberFamilyID				int,

	@EmployeeId                 int,

	@MemberFamilyNumber			int,

	@Allergies                  nvarchar(50),

	@Weight                     int,

	@Age                        int,

	@DateOfbirth                datetime,

	@MemberFamilyCreatedDate	datetime,

	@MemberFamilyCreatedBy		nvarchar(50),

	@isUpdate					bit	

)
 

AS
 

BEGIN
 

IF @bit = 0

	BEGIN

		INSERT INTO YourTable (EmployeeId, MemberFamilyNumber, Allergies,Weight,Age,DateOfbirth,MemberFamilyCreatedDate,MemberFamilyCreatedBy) 

		VALUES				  (@EmployeeId, @MemberFamilyNumber, @Allergies,@Weight,@Age,@DateOfbirth,@MemberFamilyCreatedDate,@MemberFamilyCreatedBy)

	END

ELSE

	BEGIN

		UPDATE YourTable

		SET EmployeeId = @EmployeeId

			  ,MemberFamilyNumber = @MemberFamilyNumber

			  ,Allergies = @Allergies

			  ,Weight = @Weight

			  ,Age = @Age

			  ,DateOfbirth = @DateOfbirth

			  ,MemberFamilyCreatedDate = @MemberFamilyCreatedDate

			  ,MemberFamilyCreatedBy = @MemberFamilyCreatedBy

		WHERE MemberFamilyId = @MemberFamilyId

	END
 

END

Open in new window

0
 

Author Comment

by:VBBRett
ID: 24842113
Ok, so in a stored procedure it should automatically increment the primary key value if it is identity correct?  What if I want to increment a previous value of one of the integers?  How would I do that?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24842183
you cannot Update an identity value, but you can insert it by setting  SET IDENTITY_INSERT TableName ON
Can u tell us what exactly u r looking for
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24842201
<<Ok, so in a stored procedure it should automatically increment the primary key value if it is identity correct?  >>
Stored procedure or not. If you have an IDENTITY column to the vaue will increment every time a record is inserted.
<<What if I want to increment a previous value of one of the integers?  How would I do that?>>
You will not be able to update a records value in the IDENTITY column because duplicates are not allowed. For example if the seed value currecntly is sitting at 200 and you try to update a records value from 150 to 151, you will get an error message.
To insert a valid value in the IDENTITY COLUMN for a new record you wil have to SET IDENTITY INSERT to ON.
P.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:VBBRett
ID: 24842276
How about this?  And what is that bit value that you put into your stored procedure?  I am getting an error on that.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <user>
-- Create date: <07/13/09>
-- Description:      <Insert Employee Family Information>
-- =============================================
CREATE 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)      
AS
BEGIN

IF @bit = 0
      BEGIN

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

ELSE
      BEGIN
      UPDATE MemberFamilyInfo
            SET EmployeeId = @EmployeeId
                    ,MemberFamilyNumber = @MemberFamilyNumber
                    ,Allergies = @Allergies
                    ,Weight = @Weight
                    ,Age = @Age
                    ,DateOfbirth = @DateOfbirth
                    ,MemberFamilyCreatedDate = @MemberFamilyCreatedDate
                    ,MemberFamilyCreatedBy = @MemberFamilyCreatedBy
            WHERE MemberFamilyId = @MemberFamilyId
      END

END
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24842324
Did you copy my stored proc properly. You are missing the "@isUpdate" parameter from your list. I also see that in the insert statement your VALUES statement is incomplete. The bit value helps you decide whether you are inserting a new record or updating an existing one.
P.
0
 

Author Comment

by:VBBRett
ID: 24842416
You mean this?  

CREATE PROCEDURE spUpdateMyTable(
      @MemberFamilyID                        int,
      @EmployeeId                 int,
      @MemberFamilyNumber                  int,
      @Allergies                  nvarchar(50),
      @Weight                     int,
      @Age                        int,
      @DateOfbirth                datetime,
      @MemberFamilyCreatedDate      datetime,
      @MemberFamilyCreatedBy            nvarchar(50),
      @isUpdate                              bit      
)
 
AS
 
BEGIN
 
IF @bit = 0
      BEGIN
            INSERT INTO YourTable (EmployeeId, MemberFamilyNumber, Allergies,Weight,Age,DateOfbirth,MemberFamilyCreatedDate,MemberFamilyCreatedBy)
            VALUES                          (@EmployeeId, @MemberFamilyNumber, @Allergies,@Weight,@Age,@DateOfbirth,@MemberFamilyCreatedDate,@MemberFamilyCreatedBy)
      END
ELSE
      BEGIN
            UPDATE YourTable
            SET EmployeeId = @EmployeeId
                    ,MemberFamilyNumber = @MemberFamilyNumber
                    ,Allergies = @Allergies
                    ,Weight = @Weight
                    ,Age = @Age
                    ,DateOfbirth = @DateOfbirth
                    ,MemberFamilyCreatedDate = @MemberFamilyCreatedDate
                    ,MemberFamilyCreatedBy = @MemberFamilyCreatedBy
            WHERE MemberFamilyId = @MemberFamilyId
      END
 
END
0
 

Author Comment

by:VBBRett
ID: 24842435
What is @isupdate and how is it used in the stored procedure?  What is the logic behind this bit variable and what is going to make my logic choose between the insert and/or update statement through this bit variable?  I do not understand.
0
 

Author Comment

by:VBBRett
ID: 24842577
I ran a simple test for the a stored procedure and I did not give the primary key any values.  Here is what I got back when I tried to put in all values except for the primary key:

Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@MemberFamilyID', which was not supplied.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24842591
Yes, so just remember to update the procedure to reflect correct table and column names.
@isUpdate helps you decide wether the values are you passing are for insertiong or update. To accompalish that you will have to call the stored procedure and pass the bit value as 0 if it is an insert and 1 if it is an update. Something like this this:
-- This does an insert
EXEC dbo.spUpdateMyTable (Null, 1, 52, 'Peanuts', 250,45,'1965-05-01','2009-05-01','username',0)    -- The bit value is 0
--= This will do an Update

EXEC dbo.spUpdateMyTable (52, 1, 52, 'Peanuts', 250,45,'1965-05-01','2009-05-01','username',1)   -- The bit value is 1
P.
0
 

Author Comment

by:VBBRett
ID: 24842694
Please expand on your explanation as to why the bit statement would be needed.  I am not understanding your logic.  On another note, I am not understanding why I get an error message if I omit to put any value into the primary key column in my stored procedure even though it is an identity and should increment in value.  Please explain to me how I can avoid this error, thank you!
0
 

Author Comment

by:VBBRett
ID: 24842787
Are my questions out of the ordinary, please let me know and I will try to break them down even further.  I am thinking my questions are pretty simple but I am not sure if you understand what I need.  What I need is a value for the primary key so that the primary key value automatically increments and I need an insert statement that takes into account the MemberFamilyNumber and the EmployeeID.  

Let's say if EmployeeID = 2 and memberFamilyNumber already has a value of 1, then I want the next record to be inserted to have memberFamilyNumber to be 2.  If inserted again, I wanted the memberfamilybumber to be 3 and so on and so forth.  The memberfamilyid, which is the primary key, I want to incremement no matter what.  
0
 

Author Comment

by:VBBRett
ID: 24842793
Perhaps I should close this question and start a new question thread?  Or should I just give you guys more time?
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24842852
That's alright. I am going to follow up. Give me some time... I am stuck in a meeting.
P.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24843362
Let's talk about the bit parameter logic. So let's say that you have a front end application and on that application you have a form with fields where you can enter the values. And on the same form you have two buttons, UPDATE and INSERT. So if the information entered in the form is a new entry the user will click on INSERT and if not then he/she will click on UPDATE.
So based on which button is clicked (your application should be able to determine this) your call to the procedure will change. So if INSERT was clicked the bit value you will pass will be 0 or if UPDATE was clicked the bit value you will pass will be 1. The procedure executes either the INSERT statement or UPDATE statement based on the bit value passed to the stored proc.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<<On another note, I am not understanding why I get an error message if I omit to put any value into the primary key column>>
This is because the stored procedure is expecting 10 parameters everytime. So regardless if the call to the stored procedure is for INSERT or UPDATE you will have to provide the value. So, when caling for an insert, pass the value to the priimary identity column as NULL or if it is for an update then pass the value for the record that needs to be updated.
-- INSERT Statement (MemberFamilyID (primary and identity column) will be passed a null value and bit value will be 0)
EXEC dbo.spUpdateMyTable (Null, 1, 52, 'Peanuts', 250,45,'1965-05-01','2009-05-01','username',0)  
-- UPDATE Statement (MemberFamilyId will be provided because you are not updating based on the value provided, and bit value will be 1)
EXEC dbo.spUpdateMyTable (52, 1, 52, 'Peanuts', 250,45,'1965-05-01','2009-05-01','username',1)
Again, when calling stored procs with parameters you will have to provide the exact number of parameter value as the procedure is expecting. So in our case it does not matter the identity column value is real value or null because for our insert statement we not using that column since it will auto increment. But for our update statement we will require a valid value since it is being used to update a record.
I hope that makes it clear.
P.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 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

21 Experts available now in Live!

Get 1:1 Help Now