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
 General SQL Server Forums
 New to SQL Server Programming
 avoid duplicate data in sql server data

Author  Topic 

celv
Starting Member

6 Posts

Posted - 2015-05-07 : 03:38:24
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sectionexpenses]
(@sectionname varchar(30),
@ExpensesName varchar(max),
@Date varchar(30),
@BillNo varchar(max),
@BillAmount float,
@currentdate varchar(50),
@partydetz varchar(max),
@bdate datetime)
AS
DECLARE @Result int
BEGIN TRANSACTION
IF EXISTS
(
SELECT * FROM expenzsection WHERE sectionname=@sectionname
and partydetz=@partydetz and BillNo=@BillNo and BillAmount=@BillAmount
)


BEGIN
SELECT @Result = -1
END


ELSE
begin
insert into Expenzsection(sectionname,ExpensesName,Date,BillNo,BillAmount,currentdate,partydetz,bdate)
values
(
@sectionname,@ExpensesName,@Date,@BillNo,@BillAmount,@currentdate,@partydetz,@bdate
)
SELECT @Result = @@ERROR
end


IF @Result <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @Result



celv

Kristen
Test

22859 Posts

Posted - 2015-05-07 : 05:20:51
What happens if another user adds a duplicate row to expenzsection between your

IF EXISTS

and your

insert into Expenzsection

??

I know that situation would be very rare but if it happens it will be completely unreproducible and very hard to debug.

You could look at isolation levels, or

INSERT INTO Expenzsection(...
SELECT @sectionname,...
WHERE NOT EXISTS
(
SELECT * FROM expenzsection WHERE ...
)
SELECT @Result = CASE WHEN @@ROWCOUNT = 0 THEN -1 ELSE @@ERROR END
Go to Top of Page

celv
Starting Member

6 Posts

Posted - 2015-05-07 : 08:08:21
sir please put the sample code i can't understand it

celv
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-07 : 09:10:40
quote:
Originally posted by celv

sir please put the sample code i can't understand it

celv



Kristen posted sample code for you

Gerald Britton, MCSA
Toronto PASS Chapter
Go to Top of Page
   

- Advertisement -