Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Error Executing Stored procedure

Author  Topic 

Sirchrisak
Starting Member

19 Posts

Posted - 2005-07-27 : 10:33:27
hi guys help me check why this stored procedure is generating error that ii cannot convert numeric value to smallmoney
CREATE   PROCEDURE STP_INSERT_TRANSACTION_CASH
@TransactionTypeId int,
@Description varchar(50),
@TransactionOwnerId int,
@TransactionDate smalldatetime,
@PaymentTypeId int,
@CollectorAccountNumber varchar(30),
@AmountPaid smallmoney,
@UserId varchar(20),
@PayerCode varchar(30),
@Collector varchar(30),




@TransactionClassId int
AS
Declare @TransactionId int, @TransactionDetailId int ,@TransactionAccountingId int,@CustomerCode varchar(30),@AmountDiff smallmoney
SET @CustomerCode =@PayerCode
Begin Transaction


BEGIN
INSERT INTO [Transaction](TransactionTypeId,PaymentTypeId,TransactionClassId,TransactionOwnerId,TransactionDate,[Description])
VALUES(@TransactionTypeId,@PaymentTypeId,@TransactionClassId,@TransactionOwnerId,@TransactionDate,@Description)
SET @TransactionId = SCOPE_IDENTITY()
IF @@Error<> 0 goto Push
END

INSERT INTO TransactionDetails(TransactionId,UserId,PayerCode,Collector)
VALUES(@TransactionId,@UserId,@PayerCode,@Collector)
SET @TransactionDetailId = SCOPE_IDENTITY()

IF @@Error<> 0 goto Push

INSERT INTO TransactionAccounting(TransactionDetailId,TransactionId,CollectorAccountNumber,AmountPaid)
VALUES (@TransactionDetailId,@TransactionId,@CollectorAccountNumber,@AmountPaid)
SET @TransactionAccountingId = SCOPE_IDENTITY()

IF @@Error<> 0 goto Push

INSERT INTO PaymentLog(CustomerCode,AmountPaid,Collector,PaymentDate, TransactionAccountingId)
VALUES (@CustomerCode,@AmountPaid,@Collector,@TransactionDate,@TransactionAccountingId)

IF @@Error<> 0 goto Push

UPDATE UserAccount
SET LastAmountPaid = @AmountPaid,
@AmountDiff = CustomerBill - @AmountPaid,
LastDateOfPayment = @TransactionDate,
OutstandingBill =OutstandingBill + @AmountDiff,
CustomerBill =0
WHERE CustomerCode = @CustomerCode

IF @@Error <> 0 goto Push
Commit Transaction
Return 0

Push:
BEGIN
Rollback Transaction
RETURN 1
END

i try to execute this stored procedure with statement below
Execute STP_INSERT_TRANSACTION_CASH 1,tyuio,2,'2-4-2003',1, 20005, CAST(345655.897890888 AS smallmoney(4)),jaksalabi,sirbob,'2-3-05'

it give the following Error:Line 1: Incorrect syntax near '345655.897890888'.

Chris

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-27 : 10:38:33
Use Money or float datatype

CAST(345655.897890888 AS float)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -