stored procedure runing 2 at a time

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 2005 Forums

Transact-SQL (2005)

stored procedure runing 2 at a time

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-20 : 15:11:09

i have a stored procedure that
checks if customernumber exits
if exists it updates
if not it adds

but through this stored procedure it keeps still adding duplicate records 30 seconds apart

how could this happen?

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-20 : 15:19:22

what is the primary key of the table and can you share the code as well pls...

-Shan

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-21 : 00:40:43

primary key is id

not the customernumber
(and I don't really want to make it the primary key as there are real exceptions where there are 2)

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 02:52:14

"how could this happen?"

Two people / processes adding the record at the same time? i.e. between CHECK IF EXISTS and INSERT could another process add the same record?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-21 : 03:01:35

Two people / processes adding the record at the same time? i.e. between CHECK IF EXISTS and INSERT could another process add the same record?
Go to Top of Page

this is what i think happens - but is there anyway to avoid this?

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 03:07:59

There is some good info in Sprit1's blog on "Upsert", and in the links in the comments:

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-21 : 03:21:37

so that is basically what i'm doing but without making it a transaction
begin trans

does that help it?

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 03:30:19

Nope. Afraid not

You need to get rid of the IF NOT EXISTS then INSERT approach and do both in one step.

INSERT
Catch error - if error UPDATE instead

or you can do that as:

INSERT INTO MyTable(Col1, ...)
SELECT ... data ...
WHERE NOT EXISTS (SELECT * FROM MyTable WHERE customernumber = 9999)
check @@ROWCOUNT - 1=inserted, 0=already existed

or

UPDATE
If 0 rows updates then INSERT instead (but this may still insert a duplicate)

There are locking/blocking implications to both approaches; if you have high number of Updates and few Inserted (or the other way round) that will define which approach is best.

Creating a unique index on customernumber will prevent duplicates (but application/users will get error message)

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-21 : 03:49:17

if i do the insert where not exists -- can i make it an else update?
i need to make sure it's updated.

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 03:56:27

"can i make it an else update?"

If you want to do it in one statement you would have to use MERGE (can;t remember if that is in SQL 2005, or only added in SQL 2008)

But as I said, after the INSERT WHREE NOT EXISTS you can check @@ROWCOUNT and if it is 0 then the record already existed (i.e. there was NO insert), so you can then do UPDATE

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-21 : 04:07:40

so what is wrong with this syntax

INSERT INTO customers(mobilenumber)
values('esthertest')
WHERE NOT EXISTS (SELECT * FROM customers WHERE mobilenumber='esthertest')
select @@ROWCOUNT

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 04:31:30

You can't do INSERT ... VALUES ... WHERE

You will see in my example that the syntax is INSERT ... SELECT ... WHERE

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-22 : 04:22:48

but this in a stored procedure so i'm not inserting from a table but variable passed to the stored procedure
and i want to either insert it or update it

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 04:37:55

No table reference is needed in the example I gave you.

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 04:46:41

P.S. In case not clear:

SELECT ... data ...

can be

SELECT @Parameter1, @Parameter2, ...

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-24 : 08:08:06

thanks - got this working and so far no duplicates :)

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-25 : 11:12:14

strange thing is one record got in double today thorugh this code - but this is much better then it used to be though I want to still avoid this

the exact times they were added were: so just a few seconds apart
2010-01-25 14:03:27.117
2010-01-25 14:03:23.730

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources