avoid duplicate data in sql server data

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

celv
Starting Member

6 Posts

Posted - 2015-05-07 : 08:08:21

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

celv

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources