State 1, Procedure dbo.ExecuteOperation, Line 60 [Batch Start Line 2]
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
USE [SeaphonServer]
GO
/****** Object: StoredProcedure [dbo].[ExecuteOperation] Script Date: 5/22/2022 12:57:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ExecuteOperation]
@AccountTo nvarchar(17) = NULL,
@AccountFrom nvarchar(17) = NULL,
@OperationType int = NULL,
@Cash money = NULL,
@ActionID int = NULL
AS
DECLARE @AccountFromID int
DECLARE @AccountToID int
DECLARE @FromCash Money
DECLARE @ToCash Money
DECLARE @FF NVARCHAR
BEGIN TRANSACTION
SET @AccountFromID = (SELECT dbo.Accounts.ID
FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.AccountFrom)
SET @AccountToID = (SELECT dbo.Accounts.ID
FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.AccountTo)
SET @FromCash = (SELECT dbo.Accounts.Cash
FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.AccountFrom)
SET @ToCash = (SELECT dbo.Accounts.Cash
FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.AccountTo)
SET @FF = (SELECT FullName
FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.ID)
IF (@FF IS NOT NULL)
BEGIN
IF (@AccountTo is NOT NULL AND @AccountFrom is NOT NULL) AND (@FromCash >= @ToCash)
BEGIN
UPDATE Accounts SET Cash = @FromCash - @ToCash WHERE ID = @AccountFromID;
UPDATE Accounts SET Cash = @ToCash + @FromCash WHERE ID = @AccountToID;
END
ELSE
BEGIN
ROLLBACK
END
END
ELSE
BEGIN
IF (@ActionID = 1) AND (@FromCash >= @Cash) AND (@ToCash >= @Cash)
BEGIN
UPDATE Accounts SET Cash = @FromCash - @Cash WHERE ID = @AccountFromID;
UPDATE Accounts SET Cash = @ToCash - @Cash WHERE ID = @AccountToID;
END
ELSE
BEGIN
ROLLBACK
END
IF (@ActionID = 2)
BEGIN
UPDATE Accounts SET Cash = @FromCash + @Cash WHERE ID = @AccountFromID;
UPDATE Accounts SET Cash = @FromCash + @Cash WHERE ID = @AccountToID;
END
END
COMMIT TRANSACTION
INSERT INTO Operations(AccountFrom, AccountTo, OperationType, OperationDate, OperationTime, Cash)
VALUES (@AccountFromID, @AccountToID, @OperationType, CONVERT(date, CURRENT_TIMESTAMP), CONVERT(time, CURRENT_TIMESTAMP), @Cash)
вот это SET @AccountFromID = (SELECT dbo.Accounts.ID FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.AccountFrom) SET @AccountToID = (SELECT dbo.Accounts.ID FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.AccountTo) SET @FromCash = (SELECT dbo.Accounts.Cash FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.AccountFrom) SET @ToCash = (SELECT dbo.Accounts.Cash FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.AccountTo) SET @FF = (SELECT FullName FROM dbo.Accounts INNER JOIN dbo.Operations ON dbo.Accounts.AccountID = dbo.Operations.ID) очень плохо, записей может быть более одной
INIQUE и PRIMARYKEY поля.
И.. Вот ещё. А как можно упростить такое количество подзапросов?
Обсуждают сегодня