|
su.dbms.sql- SU.DBMS.SQL ------------------------------------------------------------------ From : Akzhan Abdulin 2:5030/217 17 Feb 2001 16:04:02 To : All Subject : деревья 3 -------------------------------------------------------------------------------- Здесь скpипты - смена владельца данного счёта === Cut === -- это можно сделать иначе, то эта пpоцедуpа выполняется pедко SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE akRemoveSubAccountAncestors @AccountId AnAccountId AS BEGIN DECLARE @ChildId AnAccountId /* initialize execution state */ DECLARE @Result INT SET NOCOUNT ON SET @Result = 0 DECLARE SubAccountsCursor CURSOR LOCAL FAST_FORWARD FOR SELECT "Id" FROM ASubAccount WHERE FolderAccountId = @AccountId BEGIN TRANSACTION OPEN SubAccountsCursor FETCH NEXT FROM SubAccountsCursor INTO @ChildId WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE @Result = akRemoveSubAccountAncestors @ChildId IF @Result <> 0 GOTO fin FETCH NEXT FROM SubAccountsCursor INTO @ChildId END DELETE FROM AnAccountAndItsAncestors WHERE AccountId = @AccountId SET @Result = @@ERROR /* finalize transaction */ fin: CLOSE SubAccountsCursor DEALLOCATE SubAccountsCursor IF @@TRANCOUNT > 0 BEGIN IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION END RETURN @Result END GO -- дальше SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE akUpdateSubAccountAncestors @AccountId AnAccountId AS BEGIN DECLARE @ChildId AnAccountId /* initialize execution state */ DECLARE @Result INT SET NOCOUNT ON SET @Result = 0 DECLARE SubAccountsCursor CURSOR LOCAL FAST_FORWARD FOR SELECT "Id" FROM ASubAccount WHERE FolderAccountId = @AccountId BEGIN TRANSACTION OPEN SubAccountsCursor EXECUTE @Result = akUpdateSingleSubAccountAncestors @AccountId IF @Result <> 0 GOTO fin FETCH NEXT FROM SubAccountsCursor INTO @ChildId WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE @Result = akUpdateSubAccountAncestors @ChildId IF @Result <> 0 GOTO fin FETCH NEXT FROM SubAccountsCursor INTO @ChildId END /* finalize transaction */ fin: CLOSE SubAccountsCursor DEALLOCATE SubAccountsCursor IF @@TRANCOUNT > 0 BEGIN IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION END RETURN @Result END GO -- а вот pади чего мы писали вышеуказанное SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE akChangeSubAccountAncestor @AccountId AnAccountId, @NewAncestorId AnAccountId, @CheckRights BIT = 1 AS BEGIN /* initialize execution state */ DECLARE @Result INT SET NOCOUNT ON SET @Result = 0 BEGIN TRANSACTION IF EXISTS(SELECT * FROM AnAccountAndItsAncestors AAIA WHERE (AAIA.AccountId = @NewAncestorId) AND (AAIA.AncestorId = @AccountId)) BEGIN /* Cannot be moved under self children */ SET @Result = -500 GOTO fin END IF (@CheckRights = 1) AND (dbo.akHasConstraint('Accounting:Plan:Change') = 0) BEGIN SET @Result = -100 GOTO fin END IF EXISTS(SELECT * FROM ASubAccount WHERE ("Id" = @AccountId) AND (FolderAccountId <> @NewAncestorId)) BEGIN EXECUTE @Result = akRemoveSubAccountAncestors @AccountId IF @Result <> 0 GOTO fin UPDATE ASubAccount SET FolderAccountId = @NewAncestorId WHERE ("Id" = @AccountId) SET @Result = @@ERROR IF @Result <> 0 GOTO fin EXECUTE @Result = akUpdateSubAccountAncestors @AccountId END /* finalize transaction */ fin: IF @@TRANCOUNT > 0 BEGIN IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION END RETURN @Result END GO === Cut === Akzhan --- FMail/Win32 1.42/g * Origin: MT Computers, mailto:akzhan@mtgroup.ru (2:5030/217) Вернуться к списку тем, сортированных по: возрастание даты уменьшение даты тема автор
Архивное /su.dbms.sql/22713a8e9386.html, оценка из 5, голосов 10
|