Solved

Update Date get updated in all rows

Posted on 2011-03-24
5
154 Views
Last Modified: 2012-05-31
hi,

This is the store proc am using to update and insert my table (credit goes to AngelIII),i have column in my table called updatedDate,when ever the record is get updated it will leave the time stamp there,but whenever the store proc runs it will update all the UpdatedDate column.

I am trying to update the UpdatedDate column only the record gets updated,some where am missing some thing,please correct me.

Thanks in Advance

ALTER PROC [dbo].[InsertPhone]
( @EmployeeNo int
, @PhoneNumber char(24)
)
as
begin
declare @CONTEXT_INFO varchar(100)
select @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)

UPDATE o 
  SET o.employeeno = c.employeeno 
    , o.PhoneNumber =c.PhoneNumber
,o.DateUpdated=getdate()
    , o.CreatedBy=@CONTEXT_INFO
    , O.UpdatedBy=@CONTEXT_INFOT 
 FROM Phone o 
 JOIN ADUser c 
   ON c.EmployeeNo = o.EmployeeNo 
WHERE c.EmployeeNo =  @EmployeeNo

IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO [Phone]
    ( employeeno
    , PhoneNumber
    , CreatedBy
    , UpdatedBy
    )
   SELECT Ad.employeeno
        , Ad.PhoneNo
        , @CONTEXT_INFO
        , @CONTEXT_INFO 
     FROM ADUser AD 
    WHERE Ad.employeeno=@EmployeeNo

END

Open in new window

0
Comment
Question by:Sha1395
  • 2
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35205665
I would guess a trigger on the table (which might be coded incorrectly) ?
because the UPDATE in this proc will only change the column value for the rows based on the WHERE + JOIN clause
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35205766
You query looks ok to me. Its either a data issue you are having in your tables or  a something else is running after this (may be a trigger as angelIII mentioned)
0
 

Author Comment

by:Sha1395
ID: 35205813
Thanks for your comment,is this anyway i can check or debug ?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35205828
I would first check if there is a trigger.
other than that, I would set up a test database with just the basic things to clearly identify when/what is happening
0
 

Author Comment

by:Sha1395
ID: 35205856
I will check the trigger and thanks again.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

803 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