Databases
StackOverflow2013
Postsdbo
POHow to implement a conditional Upsert stored procedure?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to implement a conditional Upsert stored procedure?
    primarykey
    data
    text
    <p>I'm trying to implement your basic UPSERT functionality, but with a twist: sometimes I don't want to actually update an existing row.</p> <p>Essentially I'm trying to synchronize some data between different repositories, and an Upsert function seemed like the way to go. So based largely on <a href="https://stackoverflow.com/questions/13540/insert-update-stored-proc-on-sql-server/193876#193876">Sam Saffron's answer to this question</a>, as well as some other research and reading, I came up with this stored procedure:</p> <p>(note: I'm using MS SQL Server 2005, so the MERGE statement isn't an option)</p> <pre><code>CREATE PROCEDURE [dbo].[usp_UpsertItem] -- Add the parameters for the stored procedure here @pContentID varchar(30) = null, @pTitle varchar(255) = null, @pTeaser varchar(255) = null AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRANSACTION UPDATE dbo.Item WITH (SERIALIZABLE) SET Title = @pTitle, Teaser = @pTeaser WHERE ContentID = @pContentID IF @@rowcount = 0 INSERT INTO dbo.Item (ContentID, Title, Teaser) VALUES (@pContentID, @pTitle, @pTeaser) COMMIT TRANSACTION END </code></pre> <p>I'm comfortable with this for a basic Upsert, but I'd like to make the actual update conditional on the value of another column. Think of it as "locking" a row so that no further updates may be made by the Upsert procedure. I could change the UPDATE statement like so:</p> <pre><code>UPDATE dbo.Item WITH (SERIALIZABLE) SET Title = @pTitle, Teaser = @pTeaser WHERE ContentID = @pContentID AND RowLocked = false </code></pre> <p>But then the subsequent Insert would fail with a unique constraint violation (for the ContentID field) when it tries to insert a row that already exists but wasn't updated because it was "locked".</p> <p>So does this mean that I no longer have a classic Upsert, i.e. that I'll have to select the row every time to determine whether it can be updated or inserted? I'm betting that's the case, so I guess what I'm really asking for is help getting the transaction isolation level correct so that the procedure will execute safely.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USMatt
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to implement a conditional Upsert stored procedure?
      1. USTech Xie
      1. VTFavorite
    1. COWhat is RowLocked in (AND RowLocked = false)? Is it a column in your table?
      singulars
      1. POHow to implement a conditional Upsert stored procedure?
      1. USA-K
    2. CO@AlexKuznetsov - Yes, RowLocked is supposed to be a table column; in reality, there are a couple columns that dictate whether or not a row should be "locked" (i.e. not updated by this procedure) but I simplified my SQL to try to make my question more clear; got a little sloppy with the syntax though - it should, of course, be "AND RowLocked = 0" and I should have mentioned it's a bit column.
      singulars
      1. POHow to implement a conditional Upsert stored procedure?
      1. USMatt
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload