stored procedures - SQL Server Forums

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

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 .

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

SMVAKS
Starting Member

3 Posts

Posted - 2007-05-28 : 16:54:01

thanks a lot

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

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().

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.

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"

)

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.

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!

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?