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

Update urTableName
SET ColumnName = 'NewValue'
WHERE MemberFamilyID  =  valueHere

But the MemberFamilyID is a incrementing primary key.  How do I make it work when I am doing an insert?

I need this to be done as a stored procedure please.

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

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.

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

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?

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

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

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

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.

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

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.

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.

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.

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!

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.  

Perhaps I should close this question and start a new question thread?  Or should I just give you guys more time?

That's alright. I am going to follow up. Give me some time... I am stuck in a meeting.
P.

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.