Stored Procedures - Updating multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedures - Updating multiple tables

Author

Topic

buck1107
Starting Member

6 Posts

Posted - 2011-09-28 : 16:37:26

Hi,
I'm using the below stored procedure, but it isn't updating the tables as intended. It is designed to get data from the outer loop table and use that data in the several updates within the loop. Thanks for any help.

[CODE]
USE [cop]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE prcNestedLoopExample
AS

SET NOCOUNT ON

-- declare all variables
DECLARE @iReturnCode int,
@iNextCourseRowId int,
@iCurrentCourseRowId int,
@iCourseLoopControl int,
@MainTopicNum int,
@WeekNum int,
@TempGUID varchar(200)

--Initialize variables
SELECT @iCourseLoopControl = 1

SELECT @iNextCourseRowId = MIN(CourseId)
FROM AMS_ContentOverviewTopic

-- Make sure the table has data.
IF ISNULL(@iNextCourseRowId,0) = 0
BEGIN
SELECT 'No data in found in table!'
RETURN
END

-- Retrieve the first row
SELECT @iCurrentCourseRowId = CourseID,
@MainTopicNum = MainTopicNum,
@WeekNum = WeekNum
FROM AMS_ContentOverviewTopic

WHERE CourseID = @iNextCourseRowId

-- Start the main processing loop.

WHILE @iCourseLoopControl = 1

BEGIN

-- Begin the nested(inner) loop.
SELECT @TempGUID = NEWID()

UPDATE AMS_ContentOverviewTopic
SET MainTopic_GUID = @TempGUID
WHERE
(CourseID = @iCurrentCourseRowId)
AND
(WeekNum = @WeekNum)
AND
(MainTopicNum = @MainTopicNum)

UPDATE AMS_ContentOverviewSubTopic
SET MainTopic_GUID = @TempGUID
WHERE
(CourseID = @iCurrentCourseRowId)
AND
(WeekNum = @WeekNum)
AND
(MainTopicNum = @MainTopicNum)

UPDATE AMS_ContentOverviewLecture
SET MainTopic_GUID = @TempGUID
WHERE
(CourseID = @iCurrentCourseRowId)
AND
(WeekNum = @WeekNum)
AND
(MainTopicNum = @MainTopicNum)

UPDATE AMS_ContentOverviewAssessment
SET MainTopic_GUID = @TempGUID
WHERE
(CourseID = @iCurrentCourseRowId)
AND
(WeekNum = @WeekNum)
AND
(MainTopicNum = @MainTopicNum)

UPDATE AMS_ContentOverviewAssessmentFile
SET MainTopic_GUID = @TempGUID
WHERE
(CourseID = @iCurrentCourseRowId)
AND
(WeekNum = @WeekNum)
AND
(MainTopicNum = @MainTopicNum)

--Reset outer looping variables.
SELECT @iNextCourseRowId = NULL

-- Get the next iRowId.
SELECT @iNextCourseRowId = MIN(CourseID)
FROM AMS_ContentOverviewTopic
WHERE CourseID > @iCurrentCourseRowId

--Did we get a valid next row id?

IF ISNULL(@iNextCourseRowId,0) = 0
BEGIN
BREAK
END

--Get the next row.

SELECT
@iCurrentCourseRowId = CourseID,
@MainTopicNum = MainTopicNum,
@WeekNum = WeekNum
FROM AMS_ContentOverviewTopic
WHERE CourseID = @iNextCourseRowId

END

RETURN
GO

[/CODE]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-28 : 17:11:05

You don't need to loop through this updating rows one at a time. This should give you the same result:


UPDATE AMS_ContentOverviewTopic SET 
       MainTopic_GUID = newid()
       
update st set
       st.mainTopic_guid = t.mainTopic_guid
from   AMS_ContentOverviewSubTopic st
join   AMS_ContentOverviewTopic t
       on t.CourseID = st.CourseID 
       and t.WeekNum = st.WeekNum
       and t.MainTopicNum = st.MainTopicNum
       
update st set
       st.mainTopic_guid = t.mainTopic_guid
from   AMS_ContentOverviewLecture st
join   AMS_ContentOverviewTopic t
       on t.CourseID = st.CourseID 
       and t.WeekNum = st.WeekNum
       and t.MainTopicNum = st.MainTopicNum

update st set
       st.mainTopic_guid = t.mainTopic_guid
from   AMS_ContentOverviewAssessment st
join   AMS_ContentOverviewTopic t
       on t.CourseID = st.CourseID 
       and t.WeekNum = st.WeekNum
       and t.MainTopicNum = st.MainTopicNum
       
update st set
       st.mainTopic_guid = t.mainTopic_guid
from   AMS_ContentOverviewAssessmentFile st
join   AMS_ContentOverviewTopic t
       on t.CourseID = st.CourseID 
       and t.WeekNum = st.WeekNum
       and t.MainTopicNum = st.MainTopicNum

Be One with the Optimizer
TG

buck1107
Starting Member

6 Posts

Posted - 2011-10-03 : 12:23:32

Many thanks - worked like a charm!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-03 : 12:26:58

You're welcome. Keep thinking "set-based" :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources