Regarding some Update Stored procedure

Question

I have two tables as follows:-

Table1:

        -------------------------------------
        PageID|Content|TitleID(FK)|LanguageID
        -------------------------------------
        1     |abc     |101       |1
        2     |xyz     |102       |1
        --------------------------------------

Table2:

        -------------------------
        TitleID|Title |LanguageID
        -------------------------
        101    |Title1|1
        102    |Title2|1
        ------------------------

I don't want to add duplicates in my Table1 (content table). Like.. there can be no two pages with the same title. What check do I need to add in my Insert/Update Stored Procedure? How do I make sure duplicates are never added.

I have tried as follows:-

CREATE PROC InsertUpdatePageContent      
(
 @PageID int,      
 @Content nvarchar(2000),
 @TitleID int  
)      
AS      
BEGIN      
  IF(@PageID=-1)
  BEGIN
    IF(NOT EXISTS(SELECT TitleID FROM Table1 WHERE LANGUAGEID = @LANGUAGEID))
    BEGIN
      INSERT INTO Table1(Content,TitleID)
      VALUES(@Content,@TitleID)
    END
  END
  ELSE
  BEGIN
    IF(NOT EXISTS(SELECT TitleID FROM Table1 WHERE LANGUAGEID = @LANGUAGEID))
    BEGIN
      UPDATE Table1 
      SET Content = @Content, TitleID = @TitleID 
      WHERE PAGEID = @PAGEID
    END
  END 
END

Now what is happening is that it is inserting new records alright and won't allow duplicates to be added but when I update its giving me problem.

On my aspx Page I have a drop down list control that is bound to DataSource that returns Table 2 (title table) and I have a text box in which user types Page's content to be stored.

When I update, like lets say I have a row in my Table 1 as shown above with PageID=1.

Now when I am updating this row, like I didn't change the Title from the drop down and only changed Content in the text box, its not updating the record ..and when Stored procedure's Update Query does not execute it displays a Label that says "Page with this title exists already."

So whenever I am updating an existing record that label is displayed on screen.How do I change that IF condition in my Update stored procedure??

EDIT:

@gbn: will that IF condition work in case of update? I mean lets say I am updating the Page with TitleID=1, I changed its content, then when I update, it's gonna execute that IF condition and it still won't update coz TitleID=1 already exits!It will only update if TitleID=1 is not there in Table1. Isn't it? Guess I am getting confused. Please answer. Thanks.

WHat version of SQL Server are you using? If 2008+, I highly recommend the new MERGE syntax. Not really an answer (hence my use of the comment field), but would open the door for alternate coding solutions... — Stuart Ainsworth, Dec 28 '10 at 20:41
In your procedure, from where do you get @LANGUAGEID value? I don't see this parameter declared anywhere. Should it be "WHERE TitleID = @TitleID"? — Anil Soman, Dec 29 '10 at 06:04

score 2 · Answer 1 · edited Nov 09 '19 at 17:36

2

Applying a UNIQUE key constraint on TitleID in Table 1 would ensure that no duplicate values for TitleID get saved into the table.

And as far as your stored procedure's code is concerned, gbn's reply highlights the conditional check to be corrected to make it work.

edited Nov 09 '19 at 17:36

halfer

19,824
17
99
186

answered Dec 28 '10 at 20:26

Dienekes

1,548
1
16
24

In this case, no. It won't fix the "no update" issue. It will prevent duplicates but it isn't the issue here – gbn Dec 28 '10 at 20:30
^^ but why? it won't allow any duplicate TitleID in Table1 plus updation of existing records would also be possible.. – Dienekes Dec 28 '10 at 20:38
^^ I understand that your answer is correcting Serenity's attempt to write that SP for update/insert.. but what I am trying to say is that putting a UNIQUE constraint would not require any conditional logic to be written in the SP for preventing data duplication, i.e. answering his initial question of ensuring no duplicates get entered for TitleID in Table1.. – Dienekes Dec 28 '10 at 20:50
Then you have to use [TRY/CATCH](http://stackoverflow.com/questions/3593870/select-insert-version-of-an-upsert-is-there-a-design-pattern-for-high-concurre/3594328#3594328) to trap the exception. Having an insert violation could mean do an update. Without code to handle this, the stored proc will abort. Note: use @gbn to let me know you answered. A constraint by itself protects data but doesn't help the OP – gbn Dec 28 '10 at 20:54

gbn · Answer 2 · 2010-12-29T05:33:42.220

1

This will always return false

IF(NOT EXISTS(SELECT TitleID FROM Table1 WHERE LANGUAGEID=LANGUAGEID))

You are comparing a column with itself, it matches = EXISTS

It should be something like

CREATE PROC InsertUpdatePageContent (
 @PageID int,      
 @Content nvarchar(2000),
 @TitleID int
)      
AS      
IF(@PageID=-1)
BEGIN
    IF NOT EXISTS (SELECT TitleID FROM Table1 WHERE TitleID=@TitleID)
       INSERT INTO Table1(Content,TitleID) VALUES(@Content,@TitleID)
END
ELSE
    UPDATE Table1 SET Content= @Content, TitleID=@TitleID WHERE PAGEID=@PAGEID
GO

To protect your data, you should have unique index on TitleID in Table1.

edited Dec 29 '10 at 05:33

answered Dec 28 '10 at 20:24

gbn

422,506
82
585
676

Will that IF condition work in case of update? I mean lets say I am updating the Page with TitleID=1, I changed its content, then when I update, it's gonna execute that IF condition and it still won't update coz TitleID=1 already exits!It will only update if TitleID=1 is not there in Table1. Isn't it? Guess I am getting confused. Please answer.Thanks. – Serenity Dec 28 '10 at 21:47

score 1 · Accepted Answer · answered Dec 29 '10 at 06:15

For Update, you cna put this condition to avoid same titles for multiple pages..

IF NOT EXISTS (SELECT 1 from Table1 where TitleId = @TitleID and PageID <> @PageID) -- This makes sure that there is no 'other page' with same title (updated from UI)
{
    UPDATE Table1          
    SET Content = @Content, TitleID = @TitleID          
    WHERE PAGEID = @PAGEID     
}

Demitrius Nelon · Answer 4 · 2010-12-28T20:28:12.427

I am not sure I completely understand, but I'll take a shot at it for you. I will assume you can't change your data structure.

First I would put a unique constraint on the Title column in the Table2. Then I would put a unique constraint on the TitleId (FK) in the Table1. This will ensure that you don't have two entries in Table1 with the same title.

Then in the stored procedure I would do the comparison against the parameters being brought in and what exists in the database so that either update or insert is being validated against what's being passed in.

I normally use stored procedures as an API between the relational data model and the object model for some software. I rarely expose database identity columns to stored procedure signatures for applications. Database identity columns get treated differently than unique identifiers that software might use to identify entities. It's hard to help much more without additional information about the rest of the application and other stored procedures that need to work together for your solution.

Regarding some Update Stored procedure

4 Answers4