SQL stored procedure problem - decimal value conversion problem

Question

I had a very strange problem in a SQL Server stored procedure.

I have two databases. One contains a database for my billing system. Another is a reporting system with aggregated data. Inside this database there is a table with generalized information about the task. When this data is created, one of the BilledToDate fields is null. I wrote a stored procedure that creates a cursor that goes through this table and gets all the job numbers. Then I look at each job number and run a query on the invoicing database to get the total invoicing charge from the job. Once I have this total, I update the BilledToDate column with this value.

The problem is that after running the stored procedure, some results are correct and some are not. There seems to be no logical explanation for why someone is right and the next one is not. I put some print statements in a stored procedure, and all the values ​​were correct. For example, for one record, the correct amount was 99,218.25, but the update added a value of 14,700.00 to the BilledToDate field. I added a varchar column to the table and populated this field. They are all correct. This makes me think this is a casting issue, but I checked and double checked my data types and they all look right. I pull my hair out on this (little is left).

My stored procedure is below. The InvoiceAmt field is decimal (16.2) in the invchead table, and I kept it throughout the process, so I don’t understand why this is happening.

ALTER PROCEDURE [dbo].[sp_CalculateBilledToDate] 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    DECLARE @JobID varchar(10)
    DECLARE @RecordID int
    DECLARE @BilledToDate decimal(16,2)

    DECLARE c1 CURSOR FOR
    SELECT JobID, RecordID
    FROM StructuralOpenBilling

    OPEN c1

    FETCH NEXT FROM c1
    INTO @JobID, @RecordID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @BilledToDate = CONVERT(money, CASE WHEN SUM(invoiceamt) > 0 THEN SUM(InvoiceAmt) ELSE 0 END)
        FROM mfgsys803.dbo.invchead
        WHERE shortchar01 = RTRIM(@JobID)

        PRINT 'Record ID: ' + CONVERT(varchar(10), @RecordID) + '  JobID: ' + RTRIM(CONVERT(varchar(10), @JobID)) + '  Billed: ' + CONVERT(varchar(10), @BilledToDate)

        UPDATE StructuralOpenBilling
        SET BilledToDate = @BilledToDate, BilledCheck = CONVERT(varchar(50), @BilledToDate)
        WHERE RecordID = @RecordID

        PRINT 'Record ID: ' + CONVERT(varchar(10), @RecordID) + '  JobID: ' + RTRIM(CONVERT(varchar(10), @JobID)) + '  Billed: ' + CONVERT(varchar(10), @BilledToDate)

        FETCH NEXT FROM c1
        INTO @JobID, @RecordID
    END

    CLOSE c1
    DEALLOCATE c1

END

Any ideas would be appreciated.

Thank.

John

Answer 1

I notice a few things that you might pay attention to. By the way, you really think too much about this - there are a few ideas.

---

SELECT @BilledToDate = CONVERT(money, CASE WHEN SUM(ISNULL(invoiceamt,0)) > 0 THEN SUM(ISNULL(InvoiceAmt,0)) ELSE 0 END)

Same as

SELECT @BilledToDate = CONVERT(money, SUM(ISNULL(invoiceamt,0)))

* NOTE: using ISNULL () in both cases - this would be important since you cannot do math with zeros.

---

No need to use a cursor. Just join your two tables together in one update statement and work on it as with a package.

UPDATE StructuralOpenBilling
SET S.BilledToDate = I.BilledToDate
FROM
      StructuralOpenBilling S
   INNER JOIN
      (SELECT shortchar01, CONVERT(money, SUM(ISNULL(invoiceamt,0))) as BilledToDate
        FROM mfgsys803.dbo.invchead) I
   ON
      S.JobID = I.shortchar01

Answer 2

Does this do what you are trying to do?

WITH inv AS
(
SELECT shortchar01, 
CONVERT(MONEY, CASE WHEN SUM(invoiceamt) > 0 THEN 
                                             SUM(InvoiceAmt) 
                                             ELSE 0 END) AS BilledToDate
FROM mfgsys803.dbo.invchead
GROUP BY shortchar01
)
 UPDATE StructuralOpenBilling
 SET BilledToDate = inv.BilledToDate, 
     BilledCheck = CONVERT(VARCHAR(50), inv.BilledToDate)
FROM StructuralOpenBilling sob
JOIN inv ON inv.shortchar01 = RTRIM(sob.JobID)