I'm inclined to think that you have decided on Ms-SQL only out of ignoring ADS. The whole thing would be much simpler if you go the ADS route. You won't have to make any changes to your code (hardly). With time you will be able to change some navigational code as well as reporting code to SQL. AND BEFORE anyone speaks about price - I find ADS 100 times less expensive than MS-SQL.
On your 2nd question, multiple SQL statements may separated by semicolons and then executed. You may refer to these as "SQL scripts". Here is one such (multi-statement) script I'm currently executing against an ADS server. You will find several Inserts, Updates, Merge as well as sql code syntax:
//This sql will fetch and post records from remit into payfile
//after that, it sets isPosted field on remits table to true
//to avoid it being posted more than once.
//The reason I'm Disabling triggers is to make posting faster
//while avoiding the saving of new balances info on the claim
//as these should not be considered into the audit-trail 4/2/2013 1:37:40 PM
//
::cPostToRemitSQL := ;
"DECLARE tbl CURSOR; \n"+;
"DECLARE tmptbl CURSOR; \n"+;
"DECLARE nDeduct NUMERIC( 10, 2 ); \n"+;
"DECLARE nCoins NUMERIC( 10, 2 ); \n"+;
"DECLARE nPatAdj NUMERIC( 10, 2 ); \n"+;
"DECLARE nInsAdj NUMERIC( 10, 2 ); \n"+;
"DECLARE nStatus INTEGER ; \n"+;
" \n"+;
"EXECUTE PROCEDURE sp_DisableTriggers( NULL, NULL, FALSE, 0 );\n"+;
"OPEN tbl AS SELECT r.[Check], r.Insurance, r.Chkdate, \n"+;
" r.account, r.ClaimKey, r.BillCode, \n"+;
" r.Srv_date, r.Rcv_date, r.Deposit_date, \n"+;
" r.amt_paid, r.adjusted, r.deduc, r.icn, \n"+;
" r.ins_assg, r.adj_reason, \n"+;
;//if patient has been charged any amount, either as a straight-out
;//deductible or in the form of an adjustment, then [billedDeduc]
;//will not be zero.
" s.pat_charge + s.pat_adjust AS [billedDeduc],\n"+;
;//" l.deduct AS [billedDeduc], \n"+;
" s.ClaimKey AS isFoundInSrvTbl, \n"+;
" s.isClosed AS isClosed, \n"+;
" s.Co_ins AS Co_ins, \n"+;
" p.ClaimKey AS isFoundInPayTbl \n"+;
" FROM remits r \n"+;
" LEFT JOIN service s ON s.ClaimKey = r.ClaimKey \n"+;
" LEFT JOIN prclines l ON l.ClaimKey = r.ClaimKey \n"+;
" AND l.proc_code = r.BillCode \n"+;
" LEFT JOIN payfile p ON p.ClaimKey = r.ClaimKey \n"+;
" AND p.proc_code = r.billcode \n"+;
" AND p.recipt_num = r.[check] \n"+;
" AND p.INS_PAY = r.amt_paid \n"+;
" AND p.Insurance = r.Insurance \n"+;
" WHERE [check] = '$1$' \n"+;
" AND r.payer = '$2$' \n"+;
" AND chkDate = '$3$' \n"+;
" AND TRIM( r.ClaimKey ) <> '' \n"+;
" AND isPosted = FALSE ; \n"+;
" \n"+;
"BEGIN TRANSACTION ; \n"+;
"WHILE FETCH tbl DO \n"+;
" \n"+;
"//----------------------------------------------------- \n"+;
;//Post remittance entries to payfile
" nDeduct = 0.00 ; \n"+;
" nPatAdj = 0.00 ; \n"+;
" nInsAdj = tbl.adjusted ; \n"+;
" ncoIns = tbl.ins_assg ; \n"+;
"//----------------------------------------------------- \n"+;
;//"//Avoid re-posting same check when re-imported or re-entered\n"+;
;//isFoundInPayTbl is being commented because I found that
;//often times a remittance will list the same claim more than once.
;//The 1st time it may be positive and the 2nd negative or
;//a combination of these. Therefore, I'm processing all lines
;//on the remittance even when the claim line already has
;//a payment posted.
;//
;//" IF /*tbl.isFoundInPayTbl IS NULL AND*/ tbl.isFoundInSrvTbl IS NOT NULL THEN \n"+;
;//
" \n"+;
" IF tbl.isFoundInSrvTbl IS NOT NULL THEN \n"+;
" \n"+;
;//if deductibles are being ignored when posting, then adjustments to
;//change patient responsibility are not processed.
;//also ignore deductible if this claim line was billed with
;//deductible already.
" IF $4$ = TRUE AND tbl.deduc > 0.00 \n"+;
" AND tbl.Co_ins = '' \n"+;
" AND tbl.[billedDeduc] = 0.00 THEN \n"+;
" nPatAdj = -tbl.Deduc; \n"+;
;//" nInsAdj = tbl.deduc ; \n"+;
" ELSEIF tbl.Co_ins <> '' AND tbl.deduc > 0.00 THEN \n"+;
" nCoIns = tbl.deduc + tbl.ins_assg ; \n"+;
;//" nInsAdj = nCoIns + tbl.adjusted; \n"+;
" END; \n"+;
" \n"+;
" INSERT INTO payfile ( ClaimKey, adm_num, Proc_code, \n"+;
" Insurance, Pay_date, \n"+;
" last_edit, Rcv_date, \n"+;
" serv_date, ins_pay, \n"+;
" ins_adjust, pat_adjust, \n"+;
" adj_reason, recipt_num, \n"+;
" co_ins_asg, operator ) \n"+;
" VALUES ( tbl.ClaimKey, tbl.Account, tbl.billcode,\n"+;
" tbl.Insurance, tbl.chkdate, \n"+;
" tbl.Deposit_date, tbl.Rcv_date, \n"+;
" tbl.Srv_date, tbl.amt_paid, \n"+;
" tbl.adjusted, nPatAdj, \n"+;
" tbl.adj_reason, tbl.[Check], \n"+;
" nCoIns, User() ); \n"+;
" \n"+;
" END; \n"+;
"END WHILE ; \n"+;
" \n"+;
"CLOSE tbl; \n"+;
;//reopen remits but this time aggregate payfile entries per claim.
;//On this pass we will post payfile aggregates to claims and insert new
;//claims status into claimsstatus table.
" \n"+;
"OPEN tbl AS SELECT ClaimKey, Insurance, \n"+;
" FileName, Srv_date, ChkDate, \n"+;
" SUM( amt_paid ) AS amt_paid \n"+;
" FROM remits r \n"+;
" WHERE [check] = '$1$' \n"+;
" AND r.payer = '$2$' \n"+;
" AND chkDate = '$3$' \n"+;
" AND TRIM( r.ClaimKey ) <> '' \n"+;
" AND isPosted = FALSE \n"+;
" GROUP BY claimkey, insurance, FileName, Srv_date, chkDate;\n"+;
" \n"+;
"//Update service.adt totals \n"+;
"WHILE FETCH tbl DO \n"+;
" \n"+;
" OPEN tmptbl AS SELECT ClaimKey, \n"+;
" SUM( ifNull( ins_pay, 0.00 ) ) AS ins_pay, \n"+;
" SUM( ifNull( ins_adjust, 0.00 ) ) AS ins_adjust, \n"+;
" SUM( ifNull( pat_adjust, 0.00 ) ) AS pat_adjust, \n"+;
" SUM( ifNull( co_ins_asg, 0.00 ) ) AS co_ins_asg \n"+;
" FROM payfile \n"+;
" WHERE ClaimKey = tbl.ClaimKey \n"+;
" GROUP BY ClaimKey ; \n"+;
" \n"+;
" IF FETCH tmptbl THEN \n"+;
" UPDATE service SET ins_paymen = tmptbl.ins_pay, \n"+;
" pat_adjust = tmptbl.pat_adjust, \n"+;
" ins_adjust = tmptbl.ins_adjust, \n"+;
" co_ins_asg = tmptbl.co_ins_asg \n"+;
" WHERE ClaimKey = tbl.ClaimKey; \n"+;
" \n"+;
" END; \n"+;
" CLOSE tmptbl; \n"+;
" \n"+;
" nStatus = NULL ; \n"+;
" OPEN tmptbl AS SELECT real_amt - ( \n"+;
" IFNULL( ins_paymen, 0.00 ) + \n"+;
" IFNULL( pat_charge, 0.00 ) + \n"+;
" ifNull( ins_adjust, 0.00 ) + \n"+;
" ifNull( co_ins_asg, 0.00 ) ) AS iBal, \n"+;
" ifNull( ins_paymen, 0.00 ) AS amt_paid \n"+;
" FROM service \n"+;
" WHERE ClaimKey = tbl.ClaimKey; \n"+;
" \n"+;
" IF FETCH tmptbl THEN \n"+;
" IF ROUND( tmptbl.iBal, 2 ) = 0.00 THEN \n"+;
" nStatus = 1; \n"+;
" ELSEIF tmptbl.iBal > 0.00 AND ROUND( tmptbl.amt_paid, 2 ) = 0.00 THEN \n"+;
" nStatus = 2 ; \n"+;
" ELSEIF tmptbl.iBal > 0.00 THEN \n"+;
" nStatus = 3 ; \n"+;
" ELSEIF tmptbl.iBal < 0.00 THEN \n"+;
" nStatus = 5 ; \n"+;
" END; \n"+;
" END; \n"+;
" CLOSE tmptbl; \n"+;
" \n"+;
" IF nStatus IS NOT NULL THEN \n"+;
" MERGE ClaimsStatus ON ClaimKey = tbl.ClaimKey AND \n"+;
" FileName = tbl.FileName AND \n"+;
" rcv_date = tbl.ChkDate \n"+;
" WHEN MATCHED THEN UPDATE SET status = nStatus \n"+;
" WHEN NOT MATCHED THEN INSERT( ClaimKey, insurance,\n"+;
" Status, FileName, Srv_date, \n"+;
" rcv_date, operator, Data ) \n"+;
" VALUES( tbl.ClaimKey, tbl.Insurance, \n"+;
" nStatus, tbl.FileName, \n"+;
" tbl.Srv_date, tbl.ChkDate, \n"+;
" User(), 'Remittance Posting. ' );\n"+;
" \n"+;
" UPDATE service SET laststatus = nStatus, //All is good \n"+;
" lastActivity = tbl.Chkdate \n"+;
" WHERE ClaimKey = tbl.ClaimKey \n"+;
" AND ( lastActivity IS NULL \n"+;
" OR lastActivity <= tbl.Chkdate \n"+;
;//It is possible for 277s responses to be received as an answer to a
;//276 with a "4" status (white flag) and have an 835 with an older
;//date be posted. Payment status of 1 should take precedence even when
;//277 with received status (white flag) has a more recent date.
" OR nStatus = 1 ); \n"+;
" \n"+;
" END ; \n"+;
"END; \n"+;
;
"//----------------------------------------------------- \n"+;
"//Flag each entry as isPosted = TRUE \n"+;
"UPDATE remits SET isPosted = TRUE \n"+;
" WHERE [check] = '$1$' \n"+;
" AND payer = '$2$' \n"+;
" AND chkDate = '$3$' \n"+;
" AND isPosted = FALSE \n"+;
" AND EXISTS ( SELECT s.ClaimKey \n"+;
" FROM service s \n"+;
" WHERE s.ClaimKey = remits.ClaimKey ); \n"+;
" \n"+;
"COMMIT; \n"+;
" \n"+;
"CLOSE tbl; \n"+;
"EXECUTE PROCEDURE sp_EnableTriggers( NULL, NULL, FALSE, 0 );\n"
Reinaldo.
