SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2005
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error Executing Stored Procedure

    hi guys help me check why this stored procedure is generating error that 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, 345655.897890888 ,jaksalabi,sirbob,'2-3-05'

    it give the following Error:that ther is error converting numeric to smallmoney

  2. #2
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, @AmountPaid smallmoney argument can be a number from - 214,748.3648 through +214,748.3647 while you pass 345,655.897890888. You should try money datatype instead


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •