Problem In stored procedure Not updating records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem In stored procedure Not updating records

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-25 : 10:54:43

Hi Guys,
I am using this stored procedure to insert & update records.The Insert is working fine but update is not working.I dont know what
is wrong with it.The procedure I am using is as follows :-

CREATE PROCEDURE INSERTMODIFYDATA

@COMPLAINTDATE DATETIME,
@COMPLAINTNO CHAR(50),
@BRAND VARCHAR(10)
@STORE VARCHAR(10),
@ASSIGNEDTO VARCHAR(10),
@USERNAME VARCHAR(50),
@DESCRITPION VARCHAR(500),
@COMMENTS VARCHAR(500)
AS

SELECT COMPLAINTNO FROM TBLMAIN WHERE COMPLAINTNO=@COMPLAINTNO AND COMPLAINTDATE =@COMPLAINTDATE

IF @@ROWCOUNT= 0

BEGIN

BEGIN TRANSACTION

INSERT INTO TBLMAIN(COMPLAINTDATE,COMPLAINTNO,BRAND,STORE,ASSIGNEDTO,USERNAME,DESCRITPTION,COMMENTS)

VALUES(@COMPLAINTDATE,@COMPLAINTNO,@BRAND,@STORE,@ASSIGNEDTO,@USERNAME,@DESCRITPION,@COMMENTS)

COMMIT TRANSACTION

IF @@ERROR<>0

BEGIN

RAISERROR('TRANSACTION COULD NOT TK PLACE.PLEASE TRY AGAIN',16,1)

ROLLBACK TRANSACTION

END

ELSE

BEGIN

BEGIN TRANSACTION

UPDATE TBLMAIN SET

COMPLAINTDATE=@COMPLAINTDATE,
COMPLAINTNO=@COMPLAINTNO,
BRAND=@BRAND,
STORE =@STORE,
ASSIGNEDTO =@ASSIGNEDTO ,
USERNAME =@USERNAME,
DESCRITPION =@DESCRITPION,
COMMENTS =@COMMENTS

WHERE COMPLAINTNO=@COMPLAINTNO AND COMPLAINTDATE =@COMPLAINTDATE

COMMIT TRANSACTION
END

IF @@ERROR<>0
BEGIN

RAISERROR('TRANSACTION COULD NOT TK PLACE.PLEASE TRY AGAIN',16,1)

ROLLBACK TRANSACTION

END

END
GO

Any help is greatly appreciated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-25 : 11:06:33

Is the COMPLAINTDATE contain date only or date & time ? Is the date & time pass in correctly ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-25 : 11:08:43

And why are you setting the COMPLAINDATE & COMPLAINNO again ? This is not required


UPDATE TBLMAIN SET
    COMPLAINTDATE = @COMPLAINTDATE,
    COMPLAINTNO   = @COMPLAINTNO,
    BRAND         = @BRAND,
    STORE         = @STORE,
    ASSIGNEDTO    = @ASSIGNEDTO ,
    USERNAME      = @USERNAME,
    DESCRITPION   = @DESCRITPION,
    COMMENTS      = @COMMENTS
WHERE COMPLAINTNO  = @COMPLAINTNO 
AND   COMPLAINTDATE = @COMPLAINTDATE

AND the UPDATE statement is only executed when @@ROWCOUNT = 0 which will not occurs as it would fullfill the SELECT statement and @@ROWCOUNT will not be 0.

quote:
SELECT COMPLAINTNO FROM TBLMAIN WHERE COMPLAINTNO=@COMPLAINTNO AND COMPLAINTDATE =@COMPLAINTDATE

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-25 : 13:26:38

The update is executed if the insert commit does not fail.
i.e. the update will only be executed if the insert is performed.

There is no need to put a transaction round a single statement as every sql statement is atomic.
Your select will generate a resultset which is a waste of resources.
the code could be


if not exists (SELECT * FROM TBLMAIN WHERE COMPLAINTNO=@COMPLAINTNO AND COMPLAINTDATE =@COMPLAINTDATE)
begin
  INSERT INTO TBLMAIN(COMPLAINTDATE,COMPLAINTNO,BRAND,STORE,ASSIGNEDTO,USERNAME,DESCRITPTION,COMMENTS)
  VALUES(@COMPLAINTDATE,@COMPLAINTNO,@BRAND,@STORE,@ASSIGNEDTO,@USERNAME,@DESCRITPION,@COMMENTS) 
  IF @@ERROR<>0 
  BEGIN
    RAISERROR('TRANSACTION COULD NOT TK PLACE.PLEASE TRY AGAIN',16,1)
    return
  END 
end
ELSE
BEGIN
  UPDATE TBLMAIN SET 
        BRAND=@BRAND,
        STORE =@STORE,
        ASSIGNEDTO =@ASSIGNEDTO ,
        USERNAME =@USERNAME,
        DESCRITPION =@DESCRITPION,
        COMMENTS =@COMMENTS 
  WHERE COMPLAINTNO=@COMPLAINTNO AND COMPLAINTDATE =@COMPLAINTDATE 
  IF @@ERROR<>0 
  BEGIN
    RAISERROR('TRANSACTION COULD NOT TK PLACE.PLEASE TRY AGAIN',16,1)
    return
  END 
end
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-25 : 16:23:00

quote:
Originally posted by ayamas

Hi Guys,
I am using this stored procedure to insert & update records.The Insert is working fine but update is not working.I dont know what
is wrong with it.The procedure I am using is as follows :-

CREATE PROCEDURE INSERTMODIFYDATA

@COMPLAINTDATE DATETIME,
@COMPLAINTNO CHAR(50),
@BRAND VARCHAR(10)
@STORE VARCHAR(10),
@ASSIGNEDTO VARCHAR(10),
@USERNAME VARCHAR(50),
@DESCRITPION VARCHAR(500),
@COMMENTS VARCHAR(500)
AS

SELECT COMPLAINTNO FROM TBLMAIN WHERE COMPLAINTNO=@COMPLAINTNO AND COMPLAINTDATE =@COMPLAINTDATE

IF @@ROWCOUNT= 0

BEGIN

BEGIN TRANSACTION

INSERT INTO TBLMAIN(COMPLAINTDATE,COMPLAINTNO,BRAND,STORE,ASSIGNEDTO,USERNAME,DESCRITPTION,COMMENTS)

VALUES(@COMPLAINTDATE,@COMPLAINTNO,@BRAND,@STORE,@ASSIGNEDTO,@USERNAME,@DESCRITPION,@COMMENTS)

COMMIT TRANSACTION

IF @@ERROR<>0

BEGIN

RAISERROR('TRANSACTION COULD NOT TK PLACE.PLEASE TRY AGAIN',16,1)

ROLLBACK TRANSACTION

END

ELSE

BEGIN

BEGIN TRANSACTION

UPDATE TBLMAIN SET

COMPLAINTDATE=@COMPLAINTDATE,
COMPLAINTNO=@COMPLAINTNO,
BRAND=@BRAND,
STORE =@STORE,
ASSIGNEDTO =@ASSIGNEDTO ,
USERNAME =@USERNAME,
DESCRITPION =@DESCRITPION,
COMMENTS =@COMMENTS

WHERE COMPLAINTNO=@COMPLAINTNO AND COMPLAINTDATE =@COMPLAINTDATE

COMMIT TRANSACTION
END

IF @@ERROR<>0
BEGIN

RAISERROR('TRANSACTION COULD NOT TK PLACE.PLEASE TRY AGAIN',16,1)

ROLLBACK TRANSACTION

END

END
GO

Any help is greatly appreciated

You can't do a rollback tran after a commit tran. Either is ok.

Peter Larsson
Helsingborg, Sweden

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-26 : 01:34:23

Thank you guys for all of your valuable suggestion.I greatly appreciate it.
I tried all the methods u guys gave but still some how the updation is not happening.Please guys can u try it out practically & c for urself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources