SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 stored procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SMVAKS
Starting Member

3 Posts

Posted - 05/27/2007 :  12:03:41  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 05/27/2007 :  12:33:57  Show Profile  Reply with Quote
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 - 05/27/2007 :  12:53:03  Show Profile  Reply with Quote
thanks a lot

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

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 05/27/2007 :  13:28:56  Show Profile  Visit jezemine's Homepage  Reply with Quote
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 - 05/28/2007 :  16:54:01  Show Profile  Reply with Quote
thanks a lot
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 05/30/2007 :  00:00:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
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

South Africa
6 Posts

Posted - 07/20/2010 :  04:24:18  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/20/2010 :  04:32:56  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/20/2010 :  04:40:27  Show Profile  Reply with Quote
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

South Africa
6 Posts

Posted - 07/20/2010 :  09:17:36  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/20/2010 :  12:21:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 07/20/2010 :  13:18:47  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

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

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

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

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 07/21/2010 :  05:01:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000