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
 stored procedures

Author  Topic 

SMVAKS
Starting Member

3 Posts

Posted - 2007-05-27 : 12:03:41
hi

i would like to know is it possible to write insert and update in a single procedures.
please help me with some samples or link to some samples.

Regards
SMVAKS

Kristen
Test

22859 Posts

Posted - 2007-05-27 : 12:33:57
A SEARCH here reveals:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72835
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36464
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31805
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36477
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56148
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58353
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78607

Kristen
Go to Top of Page

SMVAKS
Starting Member

3 Posts

Posted - 2007-05-27 : 12:53:03
thanks a lot

i would also return some values is it possible too .
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-27 : 13:28:56
to return a record set, just include a SELECT statement in your proc.

to return parameter values, use OUTPUT parameters.

to return a single int, just do RETURN @myint at the end of your proc.


www.elsasoft.org
Go to Top of Page

SMVAKS
Starting Member

3 Posts

Posted - 2007-05-28 : 16:54:01
thanks a lot
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 00:00:24
<<
i would like to know is it possible to write insert and update in a single procedures.
>>

Not directly until UPSERT is available

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Nokushi0
Starting Member

6 Posts

Posted - 2010-07-20 : 04:24:18
quote:
Originally posted by SMVAKS

hi

i would like to know is it possible to write insert and update in a single procedures.
please help me with some samples or link to some samples.

Regards
SMVAKS



Don't know if it's the best way, but I usually do the following. It's not exactly a single procedure, but it certainly gets the job done.

IF NOT EXISTS (SELECT * FROM tblMyTable WHERE theField = 'someValue')
BEGIN
INSERT INTO tblMyTable(theField) VALUES('someValue')
END
ELSE
BEGIN
UPDATE tblMyTable SET theField = 'someValue' WHERE {condition}
END


If you expect a lot of records back in the NOT EXISTS test, you could substitute it for a COUNT().
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 04:32:56
Not sure it does "get the job done", it does not handle the situation where someone else inserts the row between your EXISTS test and INSERT, nor the situation where someone else deletes an existing row between the EXISTS and the UPDATE.

And what if the record already exists and someone else has updated the record in the meantime? Your change might be completely incompatible with the change that they just made. For example, a record has a stock level of 1 and you both decremented the stock level by one, and now the stock level is -1 ...

Plus you have the "cost" of the EXISTS test as well as the INSERT or UPDATE.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 04:40:27
To clarify the last point: if 99% of the actions will be inserts (or, conversely, if it is known that 99% will be updates) then it would be better to assume that state and use error test to then do the other state.

e.g.

INSERT ...
IF @@ROWCOUNT = 0 UPDATE ...

or

UPDATE ...
IF @@ROWCOUNT = 0 INSERT ...

(In first case the INSERT needs to be designed to insert zero rows if a record already exists, rather than fail with "Duplicate Primary Key Error" )
Go to Top of Page

Nokushi0
Starting Member

6 Posts

Posted - 2010-07-20 : 09:17:36
quote:
Originally posted by Kristen

To clarify the last point: if 99% of the actions will be inserts (or, conversely, if it is known that 99% will be updates) then it would be better to assume that state and use error test to then do the other state.

e.g.

INSERT ...
IF @@ROWCOUNT = 0 UPDATE ...

or

UPDATE ...
IF @@ROWCOUNT = 0 INSERT ...

(In first case the INSERT needs to be designed to insert zero rows if a record already exists, rather than fail with "Duplicate Primary Key Error" )



Good points. It does give a rather complex answer to a simple question though. I totally agree that your version is the correct one, however, it will most likely fly over the head of someone who clearly is even newer to SQL than I am.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 12:21:18
Yes, but the problem is that there will be periodic errors which will be impossible to reproduce, and which will frustrate users.

At least folk reading the thread will be aware of the problem and can consider what to do about it / whether they need / want to, or at least to be aware that if they get occasional errors that may be the cause.

Bit like the dangers of using NOLOCK ... best I don't start on that rant!
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-20 : 13:18:47
quote:
Originally posted by madhivanan

<<
i would like to know is it possible to write insert and update in a single procedures.
>>

Not directly until UPSERT is available

Madhivanan

Failing to plan is Planning to fail

Maybe the MERGE statement?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 14:53:59
Indeed (assuming using a recent version of SQL - did MERGE come in in SQl2005, or SQL2008 ?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-20 : 15:15:05
LOL, just noticed this thread was from 2007.. :)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-21 : 05:01:24
quote:
Originally posted by Kristen

Indeed (assuming using a recent version of SQL - did MERGE come in in SQl2005, or SQL2008 ?


It is available from vers 2008 onwards

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -