Databases
StackOverflow2013
Postsdbo
PORegarding some Update Stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORegarding some Update Stored procedure
    primarykey
    data
    text
    <p>I have two tables as follows:-</p> <p><strong>Table1:</strong></p> <pre><code> ------------------------------------- PageID|Content|TitleID(FK)|LanguageID ------------------------------------- 1 |abc |101 |1 2 |xyz |102 |1 -------------------------------------- </code></pre> <p><strong>Table2:</strong></p> <pre><code> ------------------------- TitleID|Title |LanguageID ------------------------- 101 |Title1|1 102 |Title2|1 ------------------------ </code></pre> <p>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. </p> <p>I have tried as follows:-</p> <pre><code>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 </code></pre> <p>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.</p> <p>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. </p> <p>When I update, like lets say I have a row in my Table 1 as shown above with PageID=1.</p> <p>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." </p> <p>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??</p> <p><strong>EDIT:</strong></p> <p>@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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USSerenity
    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. This table or related slice is empty.
    1. COWHat 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...
      singulars
      1. PORegarding some Update Stored procedure
      1. USStuart Ainsworth
    2. COIts SQL Server 2005
      singulars
      1. PORegarding some Update Stored procedure
      1. USSerenity
    3. COIn your procedure, from where do you get @LANGUAGEID value? I don't see this parameter declared anywhere. Should it be "WHERE TitleID = @TitleID"?
      singulars
      1. PORegarding some Update Stored procedure
      1. USAnil Soman
 

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