I have a stored procedure which is run weekly. This code is ~3000 lines long. Basically, it is the same 20 lines repeaded over 100 times with changes in the WHERE statement. This does exactly what I want but I am thinking there might be a better way? Here is a sample:INSERT INTO INVOICES (INVNUM, PROVIDER, DIVISION, BA, AGE, SEX, TES_CRE_DT, INV_CRE_DT, INV_SER_DT, MEASURE, PERFPROVIDER, POS)SELECT distinct INVOICE, PROVIDER, DIVISION, BA, AGE_AT_DOS, SEX, TES_CRE, INV_CRE_DT,INV_SER_DT, '1', PerfProv, pos FROM IDXRawWHERE SUBSTRING(INV_SER_DT,7,4) < '2012' AND AGE_AT_DOS BETWEEN 18 AND 75 AND (DX1 IN ('250.00','250.01','648.04') OR DX2 IN ('250.00','250.01','648.04') OR DX3 IN ('250.00','250.01','648.04') OR DX4 IN ('250.00','250.01','648.04')) AND CPT IN ('97802','97803','97804','G0271')INSERT INTO PQRI_MAIN (INVOICE, TXN_SER_DT, TES_CREATED, IDXUSER, CPT, MOD1, DX1, DX2, DX3, DX4, MEASURE)SELECT INVOICE, TXN_SER_DT, TES_CRE, IDXUSER, CPT, MOD1, DX1, DX2, DX3, DX4, '1' FROM IDXRaw A INNER JOIN INVOICES B ON B.INVNUM = A.INVOICE WHERE SUBSTRING(A.INV_SER_DT,7,4) < '2012' AND B.MEASURE = '1'These 2 INSERT blocks are repeated. The 1st INSERT has a completely different WHERE statement for each instance. The second INSERT is only different by the B.MEASURE = '1' (the value of B.MEASURE is different each time.I then have a similar stored procedure for 2012 data where the WHERE statements are completely different. So, this 3000 lines of code is duplicated to make 6000. Any suggestions? Is more data or explanation needed?Thanks,Lee
Suggestions on how to clean up or streamline this
Posted - 2012-01-24 : 09:58:36
