CODEWITHSUNDEEP
sql-serverstored-procedures
SaraniO
SaraniO

Reputation: 607

How to prevent duplicate update in sql stored procedure

I have this table in Sql and in insert Form of my project I prevent the user from entering same HDate for Same HNumber

RecID       HDate        HNumber     HComb
----------------------------------------------
1           2017-1-30       1          12
3           2017-1-29       1          15
5           2017-1-30       2          12
6           2017-1-30       3          12
9           2017-1-30       4          12

But in Edit Form I don't know how to prevent that,

I try this code in stored procedure but it work for some HNumber, But it prevent some other HNumber to be edited in it's own date

Create Procedure UpdCombHarByRecID

@RecID int,
@HarvestDate Date,
@HiveNumber int,
@HoneyComb Float, 

as

if NOT Exists (Select * From tHoneyHarvest Where RecID=@RecID)
return 0   
//there is no record to be updated

if Exists (Select * From tHoneyHarvest Where HarvestDate=@HarvestDate AND
 HiveNumber=HiveNumber And
 RecID!=@RecID) 
 // I hoped this should do the job 
 //(RecID is PrimaryKey and it is identity)
return 2

Update tHoneyHarvest 

Set HarvestDate=@HarvestDate,
HoneyType=@HoneyType,
HoneyComb=@HoneyComb,
HoneyDetails=@HoneyDetails

Where RecID=@RecID
return 1

now where is the problem?

Upvotes: 2

Views: 2717

Answers (2)

SaraniO
SaraniO

Reputation: 607

thanks for the help in comment. The problem was due to a typo in the procedure.

if Exists (Select * From tHoneyHarvest Where HarvestDate=@HarvestDate AND
  HiveNumber=@HiveNumber And
  RecID!=@RecID)

I forgot to add @ before HiveNumber

Upvotes: 1

BehrouzMoslem
BehrouzMoslem

Reputation: 9703

The best way is use non Clustered index .non Clustered index prevent the duplicate records when Inserts or update is going to occur .

CREATE UNIQUE INDEX MyIndex ON ExcelTable(HDate, HNumber)

please refer to

Upvotes: 2

Related Questions