We help IT Professionals succeed at work.

creating a Stored Procedure to update records in two joined tables

417 Views
Last Modified: 2012-06-21
Hello experts,

I was introduced to Stored Procedures on Sql Server by experts on this forum.
I was shown how to create one that inserts data in two joined tables.
Since then I 've experimeting by creating a similar SP to update data in the two tables using the first SP as a model for the update version.
Unfortunately my effort hasn't succeeded so far. The data in the first table gets updated but not in the second table.

here is the update SP:

Alter PROCEDURE [dbo].[UpdateMainRemarks]
      -- Add the parameters for the stored procedure here
      @ID int,
      @dDate date,
      @tTime time,
      @mMonth nvarchar (15),
      @nName nvarchar (255),
      @sSubject nvarchar (255),
      @tType nvarchar (15),
      @sRemarks nvarchar(Max)
      
AS
BEGIN
      SET NOCOUNT ON

UPDATE Main SET dDate=@dDate, tTime=@tTime,nName=@nName,sSubject=@sSubject,tType=@tType,mMonth=@mMonth where ID=@ID
  declare @newid int, @remarks nvarchar(Max)
   set @remarks = @sRemarks

SELECT @newid = SCOPE_IDENTITY()
IF @sRemarks != ''
   BEGIN
    Update tblRemarks Set mainID=@newid, Remarks=@remarks
   
End
END
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
A few comments:

1. Always best to declare vars at the top and do NOTHING between an update and the fetching of a @@ROWCOUNT or SCOPE_IDENTITY. You want to ensure nothing affects them since your update took place.

2. Why are you making a copy of the param @sRemarks, storing it in @remarks, checking sRemarks, but then using Remarks? Very confusing to me.

3. You've got no WHERE clause in the second update, so it will update ALLROWS with the last remark!

4. What if there is no existing remark to update? Do you want to insert it? Might need to check that. If it doesn't exist, insert it, otherwise update it.

5. You really need a transaction around all the updates. You wouldn't want one update to succeed and then the second to fail, right? You'd end up with inconsistent data, like you have right now.

6. Might want to consider NULLable parameters and checking IS NULL, instead of ''. I can't tell from the code what would be there when no remark is passed.
Alfredo Luis Torres SerranoASP .Net Developer

Commented:
SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

So could you explain in english what you need to do??

You would like to update the Main table with given Id and all the tblRmarks record based on mainID = main id???
Alfredo Luis Torres SerranoASP .Net Developer

Commented:
if the answer is yes Then

Alter PROCEDURE [dbo].[UpdateMainRemarks]
      -- Add the parameters for the stored procedure here
      @ID int,
      @dDate date,
      @tTime time,
      @mMonth nvarchar (15),
      @nName nvarchar (255),
      @sSubject nvarchar (255),
      @tType nvarchar (15),
      @sRemarks nvarchar(Max)
     
AS
BEGIN
      SET NOCOUNT ON

UPDATE Main SET dDate=@dDate, tTime=@tTime,nName=@nName,sSubject=@sSubject,tType=@tType,mMonth=@mMonth where ID=@ID
 

set @remarks = @sRemarks

IF @sRemarks != ''
   BEGIN
    Update tblRemarks Set Remarks=@remarks Where MainId = @Id
   
End
END

Author

Commented:
Success.....!
CodeCruiser, THANK YOU.....

Author

Commented:
Guys,

Thank you for your responses. I saw CodeCruiser response and got busy experimenting with it before I got a chance to see other responses.

Also the people behind the new site design should know that some of the lines overlap. I can hardly make out the names of the experts  because they are smudged by the first line of the comment's text

Author

Commented:
Thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.