I have to use a stored procedure to update some fields. The sp looks like this:
exec dbo.spMaintainConditionComponent
@Mask = 16
,@ConditionComponentUID =
,@Status = 1
,@ArchiveUser = ''
,@ArchiveDate = ''
,@ArchiveReason = 'Removed'
,@ArchiveTypeID = '464'
All of the parameters have the same value except the @ConditionComponentUID which can be obtained from this select code:
SELECT tbConditionComponent.ConditionComponentUID
FROM tbAsset INNER JOIN
tbConditionComponent ON tbAsset.AssetUID = tbConditionComponent.ParentID
WHERE (tbAsset.Status = 1)
I also need to use a 'while loop' or 'cursor' as multiple records can't be updated at the same time. Please could someone assist?
There are many ways to update the data. The worst one is 'by using loop'. If you don't have any other choice Use Cursor to LOOP through values:
DECLARE LoopCursor CURSOR FOR
SELECT tbConditionComponent.ConditionComponentUID
FROM tbAsset INNER JOIN tbConditionComponent ON tbAsset.AssetUID = tbConditionComponent.ParentID
WHERE (tbAsset.Status = 1)
DECLARE @ConditionComponentUIDTemp AS UNIQUEIDENTIFIER
OPEN LoopCursor
FETCH NEXT FROM LoopCursor INTO @ConditionComponentUIDTemp
WHILE @@FETCH_STATUS = 0
BEGIN
exec dbo.spMaintainConditionComponent
@Mask = 16
,@ConditionComponentUID = @ConditionComponentUIDTemp
,@Status = 1
,@ArchiveUser = ''
,@ArchiveDate = ''
,@ArchiveReason = 'Removed'
,@ArchiveTypeID = '464'
FETCH NEXT FROM LoopCursor INTO @ConditionComponentUIDTemp
END
CLOSE LoopCursor
DEALLOCATE LoopCursor
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다