SQL Stored Procedure Issue - Decimal Value Conversion Issue

Question

I am running into a very strange problem in a SQL Server stored procedure.

I have two databases. One contains the database for my billing system. The other is a reporting system with summarized data. Within this database there is a table with summarized job information. When this data is created, one of the fields, BilledToDate, is null. I wrote a stored procedure that creates a cursor that goes through this table and gets all of the job numbers. I then go through each job number and run a query against the billing database to get the total amount of billing that has been charged against job. Once I have this total, I update the BilledToDate column with this value.

The problem is that after running the stored procedure, some of the results are correct and some aren't. There doesn't appear to be any logical explanation as to why one is right and the next one is isn't. I put some print statements in the stored procedure and all of the values were correct. As an example, for one record the correct sum was 99,218.25 but the update put a value of 14,700.70 into the BilledToDate field. I added a varchar column to the table and populated that field. They are all correct. This leads me to believe that it is a casting problem but I checked and double checked my datatypes and they all look correct. I am pulling my hair out on this one (what little that is left).

My stored procedure is below. The InvoiceAmt field is a decimal(16,2) in the invchead table and I have kept it consistent throughout the process so I don't undertand 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.

Thanks.

John

Answer 1

I notice a few things you might look at. BTW -- you're really over-thinking this -- a few ideas about that here as well.

---

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

Is the same as

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

*NOTE the use of ISNULL() in both -- this would be important, as you can't do math on nulls.

---

Not necessary to use a cursor. Just join your two tables together in a single update statement and work on it as a batch.

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)