adamtrask
asked on
creating a Stored Procedure to update records in two joined tables
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,
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
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,
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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???
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???
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,
set @remarks = @sRemarks
IF @sRemarks != ''
BEGIN
Update tblRemarks Set Remarks=@remarks Where MainId = @Id
End
END
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,
set @remarks = @sRemarks
IF @sRemarks != ''
BEGIN
Update tblRemarks Set Remarks=@remarks Where MainId = @Id
End
END
ASKER
Success.....!
CodeCruiser, THANK YOU.....
CodeCruiser, THANK YOU.....
ASKER
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
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
ASKER
Thanks
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.