Hi @abhinav,
Try this.
Table structure
CREATE TABLE [dbo].[Customers](
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](50) NOT NULL,
[Balance] [decimal](10, 2) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Second Table
CREATE TABLE [dbo].[Customers_Transactions](
[CustomerFundId] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NOT NULL,
[Credit] [decimal](10, 2) NOT NULL,
[Debit] [decimal](10, 2) NOT NULL,
[Balance] [decimal](10, 2) NOT NULL,
CONSTRAINT [PK_Customers_Transactions] PRIMARY KEY CLUSTERED
(
[CustomerFundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Stored Procedures
--Customers_Transactions_SaveTransaction 1,0,200
CREATE PROCEDURE Customers_Transactions_SaveTransaction
@CustomerId INT
,@CreditBalance DECIMAL(10,2) = 0
,@DebitBalance DECIMAL(10,2) = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Balance DECIMAL(10,2)
SELECT @Balance = Balance
FROM Customers
WHERE CustomerId = @CustomerId
IF @CreditBalance > 0
BEGIN
SET @Balance = @Balance - @CreditBalance
INSERT INTO Customers_Transactions
(CustomerId
,Credit
,Debit
,Balance)
VALUES
(@CustomerId
,@CreditBalance
,@DebitBalance
,@Balance)
UPDATE Customers
SET Balance = @Balance
WHERE CustomerId = @CustomerId
END
ELSE IF @DebitBalance > 0
BEGIN
SET @Balance = @Balance - @DebitBalance
INSERT INTO Customers_Transactions
(CustomerId
,Credit
,Debit
,Balance)
VALUES
(@CustomerId
,@CreditBalance
,@DebitBalance
,@Balance)
UPDATE Customers
SET Balance = @Balance
WHERE CustomerId = @CustomerId
END
END
GO
