Share via

sql server on azure debug precision error

Jim Binder 0 Reputation points
2023-03-14T20:59:06.6966667+00:00

I am getting a precision error on sql server. Locally the code works fine.

Same database same proc. I can run the stored proc directly with expected values and is ok.

see next post for listings...

Issue: when calling the proc from node.js / msnodesqlv8 the precision errors happen;

only on Azure. I tried reducing the field precision to 5.2 but same error.

i am passing values like 50 30 10 etc.

The only change since last year (successful) was reinstalling my system with poss

diff version of node. Any ideas to try will be greatly appreciated. Jim

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,795 questions
0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jim Binder 0 Reputation points
    2023-03-14T20:59:27.5066667+00:00 
     


CREATE TABLE [dbo].[Claim]
(
    [Id]                  INT NOT NULL PRIMARY KEY IDENTITY, 
    [ClaimIdNumber]       NCHAR(30) NOT NULL, 
    [ClaimDescription]    NCHAR(100) NULL default ' ',

    [CustomerId]          nchar(20) not null,
    [PlanId]              NCHAR(25) NOT NULL,  
    [PatientFirst]        NCHAR(20) NOT NULL, 
    [PatientLast]         NCHAR(20) NOT NULL, 

    [Diagnosis1]          NCHAR(5) NULL     default ' ',
    [Diagnosis2]          NCHAR(5) NULL     default ' ',

    [Procedure1]          NCHAR(5) NULL     default ' ', 
    [Procedure2]          NCHAR(5) NULL     default ' ', 
    [Procedure3]          NCHAR(5) NULL     default ' ',

    [Physician]           NCHAR(35) NULL    default ' ',
    [Clinic]              NCHAR(35) NULL    default ' ',

    [DateService]         DATETIME null,
    [Service]             NCHAR(30) NULL DEFAULT ' ',  -- used release 2. now 30.  
    [Location]            NCHAR(35) NULL,
    [TotalCharge]         decimal(12,2) not null default 0.0, 
    [CoveredAmount]       decimal(12,2) not null default 0.0,  -- added release 2
    [BalanceOwed]         decimal(12,2) not null default 0.0,  -- added release 2
    [PaymentAmount]       decimal(12,2) not null default 0.0,
    [PaymentDate]         DATETIME null, 
    
    [DateAdded]           datetime null, 

    [AdjustedClaimId]     nchar(30) default ' ', 
    [AdjustingClaimId]    nchar(30) default ' ',
    [AdjustedDate]        datetime null,
    [AppAdjusting]        nchar(10) null,
    [ClaimStatus]         nchar(20) null,
    [Referral]            NCHAR(25) NULL,
    [PaymentAction]       NCHAR(15) NULL,

    [ClaimType]           NCHAR(01) NOT NULL default 'u',

    -- varable claim section

    [DateConfine]         datetime null,
    [DateRelease]         datetime null,
    [ToothNumber]         INT not null default 0,
    [DrugName]            NCHAR(35) NULL,
    [Eyeware]             NCHAR(35) NULL
 
)
    0 comments
  2. Jim Binder 0 Reputation points
    2023-03-14T20:59:49.8233333+00:00 
    USE [A45Insurance]
GO 
 

CREATE PROCEDURE [dbo].[AddNewClaim]


	@ClaimIdNumber      NCHAR(30),
	@ClaimDescription   NCHAR(100), 
	@Customerid         NCHAR(20),
	@PlanId             NCHAR(25),  -- ADDED
	@PatientFirst       NCHAR(20),
	@PatientLast        NCHAR(20),
	@Diagnosis1         NCHAR(05),
	@Diagnosis2         NCHAR(05),
	@Procedure1         NCHAR(05),
	@Procedure2         NCHAR(05), 
	@Physician          NCHAR(35),
	@Clinic             NCHAR(35),
	@DateService        DATETIME,
        @Service            NCHAR(30), 
	@TotalCharge        DECIMAL(12,2),    
	@PaymentAmount      DECIMAL(12,2),    
	@CoveredAmount      DECIMAL(12,2),  -- ADDED
        @BalanceOwed        DECIMAL(12,2),  -- ADDED
	@PaymentDate        DATETIME, 
	@DateAdded          DATETIME,
	@AdjustedClaimId    NCHAR(30),
	@AdjustingClaimId   NCHAR(30),
	@AdjustedDate       DATETIME,
	@AppAdjusting       NCHAR(10),
	@ClaimStatus        NCHAR(20), 
	@ClaimType          NCHAR(01),
	@DateConfine        DATETIME,
	@DateRelease        DATETIME,
	@ToothNumber        INT,
	@Eyeware            NCHAR(35),
        @DrugName           NCHAR(35)

AS 

	 /* Release 2 - added PlanId as input parameter
                      - added CoveredAmount and BalanceOwed as input parameters.
                      - added Service as input parameter */

	 INSERT INTO Claim      (ClaimIdNumber, ClaimDescription, CustomerId, PatientFirst, PatientLast, 
                                 Diagnosis1, Diagnosis2, 
                                 Procedure1, Procedure2,
				 Physician, Clinic, DateService, TotalCharge,
                                 PaymentAmount, PaymentDate, 
                                 CoveredAmount, BalanceOwed,
                                 DateAdded, AdjustedClaimId, AdjustingClaimId, AppAdjusting,
				 ClaimStatus, ClaimType,
				 DateConfine, DateRelease,
				 ToothNumber,
				 Eyeware,
				 DrugName,
                                 PlanId,
                                 Service) -- added as parameter rel 2.

              	 VALUES(@ClaimIdNumber, @ClaimDescription, @Customerid, @PatientFirst, @PatientLast,
	                @Diagnosis1, @Diagnosis2, 
			@Procedure1, @Procedure2,
			@Physician, @Clinic, @DateService, @TotalCharge,
			@PaymentAmount, @PaymentDate,
                        @CoveredAmount, @BalanceOwed,                  --- added 2
			@DateAdded,
			@AdjustedClaimId, @AdjustingClaimId, @AppAdjusting, 
			@ClaimStatus, @ClaimType,
			@DateConfine, @DateRelease,
			@ToothNumber,
			@Eyeware,
			@DrugName,
			@PlanId,
			@Service);   -- added releae 2.
	                 
    
	   

 
RETURN 0
    0 comments
  3. Jim Binder 0 Reputation points
    2023-03-14T21:00:54.6666667+00:00 
    

app.post('/addClaim', function(req,res) { 

    try { 

    if (!req.body) {
        
       res.status(400);
       s('bad body'); 
       return res('bad body');
    }

    debugger; 
    s("adding claim");
    s('req.body:' + req.body);

    for (var a in req.body) {
        s('server recieved body ' +  a + ' = .' + req.body[a] + '.' );
    }

    // for debugging
    var claimAddParmsInOrder =  ['ClaimIdNumber',
        'ClaimDescription',
        'CustomerId',
        'PlanId',
        'PatientFirst',
        'PatientLast',
        'Diagnosis1',
        'Diagnosis2',
        'Procedure1',
        'Procedure2',
        'Physician',
        'Clinic',
        'DateService',
        'Service',
        'TotalCharge',
        'PaymentAmount',
        'CoveredAmount',
        'BalanceOwed',
        'PaymentDate',
        'DateAdded',
        'AdjustedClaimId',
        'AdjustingClaimId',
        'AdjustedDate',
        'AppAdjusting',
        'ClaimStatus',
        'ClaimType',
        'DateConfine',
        'DateRelease',
        'ToothNumber',
        'Eyeware',
        'DrugName' 
      ]; 

       
    sql.open(connectionString, function(err,con) {

        if(err) { showError("a40 - server - failed to connect: " + err.message  
        , "server error",[connectionString],res,err);return; }; 

        var pm = con.procedureMgr();
        pm.callproc('[dbo].[AddNewClaim]',

                     [req.body.ClaimIdNumber,
                      req.body.ClaimDescription,
                      req.body.CustomerId,
                      req.body.PlanId,
                      req.body.PatientFirst,
                      req.body.PatientLast,
                      req.body.Diagnosis1,
                      req.body.Diagnosis2,
                      req.body.Procedure1,
                      req.body.Procedure2,
                      req.body.Physician,
                      req.body.Clinic,
                      req.body.DateService,
                      req.body.Service,
                      req.body.TotalCharge,
                      req.body.PaymentAmount,
                      req.body.CoveredAmount,
                      req.body.BalanceOwed,
                      req.body.PaymentDate,
                      req.body.DateAdded,
                      req.body.AdjustedClaimId,
                      req.body.AdjustingClaimId,
                      req.body.AdjustedDate,
                      req.body.AppAdjusting,
                      req.body.ClaimStatus,
                      req.body.ClaimType,
                      req.body.DateConfine,
                      req.body.DateRelease,
                      req.body.ToothNumber,
                      req.body.Eyeware,
                      req.body.DrugName 
                    ],
                     function(err,results, output) {

            if(err) {  
                s('add claim error error: ' + err.message)  
                //
                showError('a40 - server - claim add  fails: ' + err.message,
                'server error',[query],res,err);
                return;
            }  
            if(output  == null) {
                s('add new claim serious error');
                return;
            }
            // check edit failure for dup or non-existing from custId. 
            var returnCode = output[0];
            // OK , Duplicate, Missing will be output.
            var result = { code: 0, message: '' };
            s('Claim Add:procedure return code:' + output[0]);
            result.code = returnCode;
            if(result.code !== 0) { 

                res.status(500);
                res.send(null); 
            }

              // send matching customeromer object back 
           s('successful claim addition');
           res.status(200) 
           res.send(res.body)

           
        });
  
    }); 

    
   } catch (err) {

       s('/Claim Add encountered an error:' + err.message);
   }
   
});
    0 comments
  4. Jim Binder 0 Reputation points
    2023-03-15T01:58:36.6333333+00:00

    Passing the parameters as NCHAR with CONVERT on the Insert statement seems to have

    corrected this issue.

    USE [A45Insurance]
GO 
 

CREATE PROCEDURE [dbo].[AddNewClaim]


	@ClaimIdNumber      NCHAR(30),
	@ClaimDescription   NCHAR(100), 
	@Customerid         NCHAR(20),
	@PlanId             NCHAR(25),  
	@PatientFirst       NCHAR(20),
	@PatientLast        NCHAR(20),
	@Diagnosis1         NCHAR(05),
	@Diagnosis2         NCHAR(05),
	@Procedure1         NCHAR(05),
	@Procedure2         NCHAR(05), 
	@Physician          NCHAR(35),
	@Clinic             NCHAR(35),
	@DateService        DATETIME,
    @Service            NCHAR(30), 
	@TotalCharge        NCHAR(20), 
	@PaymentAmount      NCHAR(20),
	@CoveredAmount      NCHAR(20), 
    @BalanceOwed        NCHAR(20),  
	@PaymentDate        DATETIME, 
	@DateAdded          DATETIME,
	@AdjustedClaimId    NCHAR(30),
	@AdjustingClaimId   NCHAR(30),
	@AdjustedDate       DATETIME,
	@AppAdjusting       NCHAR(10),
	@ClaimStatus        NCHAR(20), 
	@ClaimType          NCHAR(01),
	@DateConfine        DATETIME,
	@DateRelease        DATETIME,
	@ToothNumber        INT,
	@Eyeware            NCHAR(35),
    @DrugName           NCHAR(35)

AS 

	 /* Release 2 - added PlanId as input parameter
                      - added CoveredAmount and BalanceOwed as input parameters.
                      - added Service as input parameter */


	 -- cast amounts into decimal(12,3)

	 INSERT INTO Claim      (ClaimIdNumber, ClaimDescription, CustomerId, PatientFirst, PatientLast, 
                                 Diagnosis1, Diagnosis2, 
                                 Procedure1, Procedure2,
				 Physician, Clinic, DateService, 
				                TotalCharge,
                                 PaymentAmount, 
								 PaymentDate, 
                                 CoveredAmount, 
								 BalanceOwed,
                                 DateAdded, AdjustedClaimId, AdjustingClaimId, AppAdjusting,
				 ClaimStatus, ClaimType,
				 DateConfine, DateRelease,
				 ToothNumber,
				 Eyeware,
				 DrugName,
                 PlanId,
                 Service) 

              	 VALUES(@ClaimIdNumber, @ClaimDescription, @Customerid, @PatientFirst, @PatientLast,
	                @Diagnosis1, @Diagnosis2, 
			@Procedure1, @Procedure2,
			@Physician, @Clinic, @DateService,
		    CAST(@TotalCharge AS DECIMAL(12,3)),
			CAST(@PaymentAmount AS DECIMAL(12,3)),
			@PaymentDate,
            CAST(@CoveredAmount AS DECIMAL(12,3)),
		    CAST(@BalanceOwed AS DECIMAL(12,3)),               
			@DateAdded,
			@AdjustedClaimId, @AdjustingClaimId, @AppAdjusting, 
			@ClaimStatus, @ClaimType,
			@DateConfine, @DateRelease,
			@ToothNumber,
			@Eyeware,
			@DrugName,
			@PlanId,
			@Service);   
	                 
    
	   

 
RETURN 0
    0 comments
  5. Jim Binder 0 Reputation points
    2023-03-15T01:59:37.39+00:00 
    USE [A45Insurance]
GO 
 

CREATE PROCEDURE [dbo].[AddNewClaim]


	@ClaimIdNumber      NCHAR(30),
	@ClaimDescription   NCHAR(100), 
	@Customerid         NCHAR(20),
	@PlanId             NCHAR(25),  
	@PatientFirst       NCHAR(20),
	@PatientLast        NCHAR(20),
	@Diagnosis1         NCHAR(05),
	@Diagnosis2         NCHAR(05),
	@Procedure1         NCHAR(05),
	@Procedure2         NCHAR(05), 
	@Physician          NCHAR(35),
	@Clinic             NCHAR(35),
	@DateService        DATETIME,
    @Service            NCHAR(30), 
	@TotalCharge        NCHAR(20), 
	@PaymentAmount      NCHAR(20),
	@CoveredAmount      NCHAR(20), 
    @BalanceOwed        NCHAR(20),  
	@PaymentDate        DATETIME, 
	@DateAdded          DATETIME,
	@AdjustedClaimId    NCHAR(30),
	@AdjustingClaimId   NCHAR(30),
	@AdjustedDate       DATETIME,
	@AppAdjusting       NCHAR(10),
	@ClaimStatus        NCHAR(20), 
	@ClaimType          NCHAR(01),
	@DateConfine        DATETIME,
	@DateRelease        DATETIME,
	@ToothNumber        INT,
	@Eyeware            NCHAR(35),
    @DrugName           NCHAR(35)

AS 

	 /* Release 2 - added PlanId as input parameter
                      - added CoveredAmount and BalanceOwed as input parameters.
                      - added Service as input parameter */


	 -- cast amounts into decimal(12,3)

	 INSERT INTO Claim      (ClaimIdNumber, ClaimDescription, CustomerId, PatientFirst, PatientLast, 
                                 Diagnosis1, Diagnosis2, 
                                 Procedure1, Procedure2,
				 Physician, Clinic, DateService, 
				                TotalCharge,
                                 PaymentAmount, 
								 PaymentDate, 
                                 CoveredAmount, 
								 BalanceOwed,
                                 DateAdded, AdjustedClaimId, AdjustingClaimId, AppAdjusting,
				 ClaimStatus, ClaimType,
				 DateConfine, DateRelease,
				 ToothNumber,
				 Eyeware,
				 DrugName,
                 PlanId,
                 Service) 

              	 VALUES(@ClaimIdNumber, @ClaimDescription, @Customerid, @PatientFirst, @PatientLast,
	                @Diagnosis1, @Diagnosis2, 
			@Procedure1, @Procedure2,
			@Physician, @Clinic, @DateService,
		    CAST(@TotalCharge AS DECIMAL(12,3)),
			CAST(@PaymentAmount AS DECIMAL(12,3)),
			@PaymentDate,
            CAST(@CoveredAmount AS DECIMAL(12,3)),
		    CAST(@BalanceOwed AS DECIMAL(12,3)),               
			@DateAdded,
			@AdjustedClaimId, @AdjustingClaimId, @AppAdjusting, 
			@ClaimStatus, @ClaimType,
			@DateConfine, @DateRelease,
			@ToothNumber,
			@Eyeware,
			@DrugName,
			@PlanId,
			@Service);   
	                 
    
	   

 
RETURN 0