wrong in my procedure [Resolved]

Posted by Selvimca under Sql Server on 4/24/2015 | Points: 10 | Views : 400 | Status : [Member] | Replies : 1
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[paidamountt]
(
@partyname varchar(max),
@billno varchar(max),
@date varchar(50),
@currentdate varchar(30),
@sectionname varchar(max),

@paymentdate varchar(50),
@paymentamount float,
@paymentcurrentdate varchar(50),
@partydetz varchar(max),

@bdate varchar(30),
@remainamount float,
@billamount float,
@ExpensesName varchar(max)
)
AS
DECLARE @Result int
DECLARE @Resultt int

BEGIN TRANSACTION

IF EXISTS
(
SELECT * FROM expenzsection WHERE sectionname=@sectionname
and partydetz=@partydetz and BillNo=@BillNo and BillAmount=@BillAmount


)
BEGIN
SELECT @Result = -1
END
IF EXISTS
(
SELECT * FROM Amountpaid WHERE partyname= @partyname
and billno=@billno and date=@date and sectionname=@sectionname and paymentamount=@paymentamount and billamount=@billamount
)
BEGIN
SELECT @Resultt = -1
END
ELSE
BEGIN
INSERT INTO expenzsection
(
sectionname,ExpensesName,date,BillNo,BillAmount,
currentdate,partydetz,bdate,remainamount
)
VALUES
(
@sectionname,@ExpensesName,@date,@BillNo,
@BillAmount,@currentdate,@partydetz,@bdate,@remainamount

)
SELECT @Result = @@ERROR
END




BEGIN
INSERT INTO Amountpaid
(
partyname,billno,date,sectionname,billamount,paymentdate,paymentamount,paymentcurrentdate,partydetz,currentdate,bdate,ExpensesName
)
VALUES
(@partyname,@billno,@date,@sectionname,@billamount,@paymentdate,@paymentamount,@paymentcurrentdate,@partydetz,@currentdate,@bdate,@ExpensesName
)
SELECT @Resultt = @@ERROR
END

IF @Result <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @Result


IF @Resultt <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @Resultt




data inserted in this table

Amountpaid

but data not inserted in expenzsection




Responses

Posted by: Bandi on: 4/24/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
As per below logic in your stored procedure,
IF EXISTS 
(
SELECT * FROM Amountpaid WHERE partyname= @partyname
and billno=@billno and date=@date and sectionname=@sectionname and paymentamount=@paymentamount and billamount=@billamount
)
BEGIN
SELECT @Resultt = -1
END
ELSE BEGIN
INSERT INTO expenzsection
(
sectionname,ExpensesName,date,BillNo,BillAmount,
currentdate,partydetz,bdate,remainamount
)
VALUES
(
@sectionname,@ExpensesName,@date,@BillNo,
@BillAmount,@currentdate,@partydetz,@bdate,@remainamount

)

SELECT @Result = @@ERROR
END


May be your input criteria is not going to ELSE block of above code part... That is the reason the data is not inserting into expenzsection table

To insert data to this table, pass input for stored procedure to doesn't satisfy below SELECT statement

SELECT * FROM Amountpaid WHERE partyname= @partyname 
and billno=@billno and date=@date and sectionname=@sectionname and paymentamount=@paymentamount and billamount=@billamount


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Selvimca, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response