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)
 
 

Вернуться к списку тем, сортированных по: возрастание даты  уменьшение даты  тема  автор