smoschkau

asked on

# Understanding SQL SP's

I have a create table procedure that runs three SP's to populate it- and I am having trouble getting them to work-

I added four more columns to the table and the first SP errors out

Msg 213, Level 16, State 1, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

Do I need all of the coumns in each SP??

If so can I default the columns to null until there data can update properly??

Null as phys_low,

Null as phys_high,

Null as Los_low,

Null as Los_high

Can someone help me answer this??

Thanks!

Susie

I added four more columns to the table and the first SP errors out

Msg 213, Level 16, State 1, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

Do I need all of the coumns in each SP??

If so can I default the columns to null until there data can update properly??

Null as phys_low,

Null as phys_high,

Null as Los_low,

Null as Los_high

Can someone help me answer this??

Thanks!

Susie

if you are not explicity planning to insert the NULLs, mention the Column list which are not null explicitly in the insert part

INSERT INTO urTable (NotNullColumn1, NotNullColumn2)

SELECT 1, 2

INSERT INTO urTable (NotNullColumn1, NotNullColumn2)

SELECT 1, 2

ASKER

Here is my code-

ALTER PROCEDURE [dbo].[populate_aprsev_icd9_medical]

@schema varchar(18)

AS

EXECUTE AS USER = @schema

;

DECLARE @cmd varchar(8000);

DECLARE @cmd2 varchar(8000);

DECLARE @facility varchar(6);

IF (@schema = 'MEDPAR')

BEGIN

DECLARE facility_cursor CURSOR

FOR

SELECT mcare_id FROM dbo.facility

WHERE mcare_id in (SELECT distinct mcare_id FROM medpar.patient)

END

ELSE

BEGIN

DECLARE facility_cursor CURSOR

FOR

SELECT mcare_id FROM dbo.facility

WHERE mcare_id in (SELECT distinct mcare_id FROM ub92.patient)

END

OPEN facility_cursor

set nocount on

FETCH NEXT FROM facility_cursor INTO @facility

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

SET @cmd = 'insert into ' + @schema + '.aprsev_icd9_summary

select mcare_id, mdc, drg, pdx as icd9, aprdrg, aprsev_ind, patient_year, patient_qtr, ''M'',

count(dkey) as discharges,

sum(los) as LOS,

max(state) as State,

sum(cast(aprmort_ind as int)) as AprMort,

sum(round(cast(aprdrg_elos as float),2)) as AprElos,

sum(round(cast(aprdrg_echg as float),2)) as AprEchg,

sum(round(cast(ecr02prob as float),2))/count(dkey) as ExpectedComplication,

sum(ecr02outc) as Complication,

sum(cast(ecr02outc as float))/count(dkey) as ActualComplication,

sum(cast(mortindex as float))/count(dkey) as ExpectedMortality,

sum(case disp when 20 then 1 else 0 end) as Mortality,

sum(case disp when 20 then 1 else 0 end)/ cast(count(dkey)as float)as ActualMortality,

sum(case when outsrc = 1 or outsrc = 2 then 1 else 0 end) as outlier,

sum(case afro when ''1'' then 1 else 0 end) as afro_physician,

sum(case afro when ''2'' then 1 else 0 end) as afro_clinic,

sum(case afro when ''3'' then 1 else 0 end) as afro_snf,

sum(case afro when ''4'' then 1 else 0 end) as afro_hospital,

sum(case afro when ''7'' then 1 else 0 end) as afro_er,

sum(case when afro = ''5'' or afro = ''6'' or afro = ''8'' or afro = ''9'' or afro = ''A'' or afro = ''B'' or afro = ''C'' then 1 else 0 end) as afro_other,

sum(case when ppay = ''MA'' or ppay = ''MD'' then 1 else 0 end) as ppay_mcare,

sum(case when ppay = ''MB'' or ppay = ''MC'' then 1 else 0 end) as ppay_mcaid,

sum(case when ppay = ''BC'' or ppay = ''CO'' then 1 else 0 end) as ppay_bcbs,

sum(case when ppay = ''HM'' then 1 else 0 end) as ppay_hmo,

sum(case when ppay = ''PP'' then 1 else 0 end) as ppay_ppo,

sum(case when ppay = ''CH'' or ppay = ''OG'' or ppay = ''WC'' then 1 else 0 end) as ppay_ogov,

sum(case when ppay = ''SP'' then 1 else 0 end) as ppay_self,

sum(case when ppay = ''OT'' or ppay = ''UK'' then 1 else 0 end) as ppay_other,

sum(case when disp = 1 then 1 else 0 end) as disp_home,

sum(case when disp = 3 then 1 else 0 end) as disp_snf,

sum(case when disp = 20 then 1 else 0 end) as disp_exp,

sum(case when disp = 6 then 1 else 0 end) as disp_hhs,

sum(case when disp <> 1 and disp <> 3 and disp <> 6 and disp <> 20 then 1 else 0 end) as disp_other,

sum(reimburse) as reimbursement,

sum(cost1) as cost1, sum(cost2) as cost2, sum(cost3) as cost3, sum(cost4) as cost4, sum(cost5) as cost5,sum(cost6) as cost6,

sum(cost7) as cost7,sum(cost8) as cost8,sum(cost9) as cost9,

sum(cost10) as cost10,sum(cost11) as cost11,sum(cost12) as cost12,

sum(cost13) as cost13,sum(cost14) as cost14,sum(cost15)as cost15,

sum(cost16) as cost16,sum(cost17) as cost17,sum(cost18) as cost18,

sum(cost19) as cost19,sum(cost20) as cost20,sum(cost21) as cost21,

sum(cost22) as cost22,sum(cost23) as cost23,sum(cost24) as cost24,

sum(cost25) as cost25,sum(cost26) as cost26,sum(cost27) as cost27,

sum(cost28) as cost28,sum(cost29) as cost29,sum(cost30) as cost30,

sum(cost31) as cost31,sum(cost32) as cost32,sum(cost33) as cost33,

sum(cost34) as cost34,sum(cost35) as cost35,sum(cost36) as cost36,

sum(chg1) as chg1,sum(chg2) as chg2,sum(chg3) as chg3,

sum(chg4) as chg4,sum(chg5) as chg5,sum(chg6) as chg6,

sum(chg7) as chg7,sum(chg8) as chg8,sum(chg9) as chg9,

sum(chg10) as chg10,sum(chg11) as chg11,sum(chg12) as chg12,

sum(chg13) as chg13,sum(chg14) as chg14,sum(chg15) as chg15,

sum(chg16) as chg16,sum(chg17) as chg17,sum(chg18) as chg18,

sum(chg19) as chg19,sum(chg20) as chg20,sum(chg21) as chg21,

sum(chg22) as chg22,sum(chg23) as chg23,sum(chg24) as chg24,

sum(chg25) as chg25,sum(chg26) as chg26,sum(chg27) as chg27,

sum(chg28) as chg28,sum(chg29) as chg29,sum(chg30) as chg30,

sum(chg31) as chg31,sum(chg32) as chg32,sum(chg33) as chg33,

sum(chg34) as chg34,sum(chg35) as chg35,sum(chg36) as chg36,

getdate()

from ('

SET @cmd2 = 'select p.mcare_id, p.dkey, p.mdc, p.drg, p.pdx, p.aprdrg, p.patient_year, p.patient_qtr, p.los, f.state, p.outsrc, p.afro, p.ppay, p.aprsev_ind, p.aprmort_ind, p.aprdrg_elos, p.aprdrg_echg, ecr02prob, ecr02outc, mortindex, disp, c.value as reimburse,

cost1,cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13,cost14,cost15,cost16,cost17,cost18,cost19,cost20,cost21,cost22,

cost23,cost24,cost25,cost26, cost27, cost28, cost29,cost30,cost31,cost32,cost33,cost34,cost35,cost36,

chg1,chg2,chg3,chg4,chg5,chg6, chg7, chg8,chg9, chg10, chg11, chg12, chg13, chg14,chg15,chg16,chg17,chg18,chg19,chg20,chg21,chg22,chg23,chg24,

chg25,chg26, chg27, chg28, chg29,chg30,chg31,chg32,chg33,chg34,chg35,chg36

from ' + @schema + '.patient p join (

select mcare_id, dkey, cost1, cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13, cost14, cost15, cost16, cost17, cost18, cost19, cost20, cost21, cost22, cost23,cost24,

cost25, cost26, cost27, cost28, cost29, cost30, cost31, cost32, cost33,

cost34, cost35, cost36, chg1, chg2, chg3, chg4, chg5, chg6, chg7, chg8, chg9, chg10, chg11, chg12, chg13, chg14, chg15, chg16, chg17, chg18, chg19, chg20, chg21, chg22, chg23, chg24, chg25, chg26, chg27, chg28, chg29, chg30, chg31, chg32, chg33, chg34, chg35, chg36

from ' + @schema + '.RCC_DeptCostChg_View) as r1

on p.mcare_id = r1.mcare_id and p.dkey = r1.dkey

join ' + @schema + '.charges c on p.mcare_id = c.mcare_id and p.dkey = c.dkey

join [dbo].facility f on f.mcare_id = p.mcare_id

where p.mcare_id = ''' + @facility + '''

and p.drg in (select drg from [dbo].drg_des where type = ''M'')

and c.charge_type = ''AMTREIM'') as d1

group by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

order by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

;'

print(@cmd+@cmd2);

exec (@cmd+@cmd2)

;

print 'finished ' + @facility;

END

FETCH NEXT FROM facility_cursor INTO @facility

END

CLOSE facility_cursor

DEALLOCATE facility_cursor

ALTER PROCEDURE [dbo].[populate_aprsev_icd

@schema varchar(18)

AS

EXECUTE AS USER = @schema

;

DECLARE @cmd varchar(8000);

DECLARE @cmd2 varchar(8000);

DECLARE @facility varchar(6);

IF (@schema = 'MEDPAR')

BEGIN

DECLARE facility_cursor CURSOR

FOR

SELECT mcare_id FROM dbo.facility

WHERE mcare_id in (SELECT distinct mcare_id FROM medpar.patient)

END

ELSE

BEGIN

DECLARE facility_cursor CURSOR

FOR

SELECT mcare_id FROM dbo.facility

WHERE mcare_id in (SELECT distinct mcare_id FROM ub92.patient)

END

OPEN facility_cursor

set nocount on

FETCH NEXT FROM facility_cursor INTO @facility

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

SET @cmd = 'insert into ' + @schema + '.aprsev_icd9_summary

select mcare_id, mdc, drg, pdx as icd9, aprdrg, aprsev_ind, patient_year, patient_qtr, ''M'',

count(dkey) as discharges,

sum(los) as LOS,

max(state) as State,

sum(cast(aprmort_ind as int)) as AprMort,

sum(round(cast(aprdrg_elos

sum(round(cast(aprdrg_echg

sum(round(cast(ecr02prob as float),2))/count(dkey) as ExpectedComplication,

sum(ecr02outc) as Complication,

sum(cast(ecr02outc as float))/count(dkey) as ActualComplication,

sum(cast(mortindex as float))/count(dkey) as ExpectedMortality,

sum(case disp when 20 then 1 else 0 end) as Mortality,

sum(case disp when 20 then 1 else 0 end)/ cast(count(dkey)as float)as ActualMortality,

sum(case when outsrc = 1 or outsrc = 2 then 1 else 0 end) as outlier,

sum(case afro when ''1'' then 1 else 0 end) as afro_physician,

sum(case afro when ''2'' then 1 else 0 end) as afro_clinic,

sum(case afro when ''3'' then 1 else 0 end) as afro_snf,

sum(case afro when ''4'' then 1 else 0 end) as afro_hospital,

sum(case afro when ''7'' then 1 else 0 end) as afro_er,

sum(case when afro = ''5'' or afro = ''6'' or afro = ''8'' or afro = ''9'' or afro = ''A'' or afro = ''B'' or afro = ''C'' then 1 else 0 end) as afro_other,

sum(case when ppay = ''MA'' or ppay = ''MD'' then 1 else 0 end) as ppay_mcare,

sum(case when ppay = ''MB'' or ppay = ''MC'' then 1 else 0 end) as ppay_mcaid,

sum(case when ppay = ''BC'' or ppay = ''CO'' then 1 else 0 end) as ppay_bcbs,

sum(case when ppay = ''HM'' then 1 else 0 end) as ppay_hmo,

sum(case when ppay = ''PP'' then 1 else 0 end) as ppay_ppo,

sum(case when ppay = ''CH'' or ppay = ''OG'' or ppay = ''WC'' then 1 else 0 end) as ppay_ogov,

sum(case when ppay = ''SP'' then 1 else 0 end) as ppay_self,

sum(case when ppay = ''OT'' or ppay = ''UK'' then 1 else 0 end) as ppay_other,

sum(case when disp = 1 then 1 else 0 end) as disp_home,

sum(case when disp = 3 then 1 else 0 end) as disp_snf,

sum(case when disp = 20 then 1 else 0 end) as disp_exp,

sum(case when disp = 6 then 1 else 0 end) as disp_hhs,

sum(case when disp <> 1 and disp <> 3 and disp <> 6 and disp <> 20 then 1 else 0 end) as disp_other,

sum(reimburse) as reimbursement,

sum(cost1) as cost1, sum(cost2) as cost2, sum(cost3) as cost3, sum(cost4) as cost4, sum(cost5) as cost5,sum(cost6) as cost6,

sum(cost7) as cost7,sum(cost8) as cost8,sum(cost9) as cost9,

sum(cost10) as cost10,sum(cost11) as cost11,sum(cost12) as cost12,

sum(cost13) as cost13,sum(cost14) as cost14,sum(cost15)as cost15,

sum(cost16) as cost16,sum(cost17) as cost17,sum(cost18) as cost18,

sum(cost19) as cost19,sum(cost20) as cost20,sum(cost21) as cost21,

sum(cost22) as cost22,sum(cost23) as cost23,sum(cost24) as cost24,

sum(cost25) as cost25,sum(cost26) as cost26,sum(cost27) as cost27,

sum(cost28) as cost28,sum(cost29) as cost29,sum(cost30) as cost30,

sum(cost31) as cost31,sum(cost32) as cost32,sum(cost33) as cost33,

sum(cost34) as cost34,sum(cost35) as cost35,sum(cost36) as cost36,

sum(chg1) as chg1,sum(chg2) as chg2,sum(chg3) as chg3,

sum(chg4) as chg4,sum(chg5) as chg5,sum(chg6) as chg6,

sum(chg7) as chg7,sum(chg8) as chg8,sum(chg9) as chg9,

sum(chg10) as chg10,sum(chg11) as chg11,sum(chg12) as chg12,

sum(chg13) as chg13,sum(chg14) as chg14,sum(chg15) as chg15,

sum(chg16) as chg16,sum(chg17) as chg17,sum(chg18) as chg18,

sum(chg19) as chg19,sum(chg20) as chg20,sum(chg21) as chg21,

sum(chg22) as chg22,sum(chg23) as chg23,sum(chg24) as chg24,

sum(chg25) as chg25,sum(chg26) as chg26,sum(chg27) as chg27,

sum(chg28) as chg28,sum(chg29) as chg29,sum(chg30) as chg30,

sum(chg31) as chg31,sum(chg32) as chg32,sum(chg33) as chg33,

sum(chg34) as chg34,sum(chg35) as chg35,sum(chg36) as chg36,

getdate()

from ('

SET @cmd2 = 'select p.mcare_id, p.dkey, p.mdc, p.drg, p.pdx, p.aprdrg, p.patient_year, p.patient_qtr, p.los, f.state, p.outsrc, p.afro, p.ppay, p.aprsev_ind, p.aprmort_ind, p.aprdrg_elos, p.aprdrg_echg, ecr02prob, ecr02outc, mortindex, disp, c.value as reimburse,

cost1,cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13,cost14,cost15,cost1

cost23,cost24,cost25,cost2

chg1,chg2,chg3,chg4,chg5,c

chg25,chg26, chg27, chg28, chg29,chg30,chg31,chg32,ch

from ' + @schema + '.patient p join (

select mcare_id, dkey, cost1, cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13, cost14, cost15, cost16, cost17, cost18, cost19, cost20, cost21, cost22, cost23,cost24,

cost25, cost26, cost27, cost28, cost29, cost30, cost31, cost32, cost33,

cost34, cost35, cost36, chg1, chg2, chg3, chg4, chg5, chg6, chg7, chg8, chg9, chg10, chg11, chg12, chg13, chg14, chg15, chg16, chg17, chg18, chg19, chg20, chg21, chg22, chg23, chg24, chg25, chg26, chg27, chg28, chg29, chg30, chg31, chg32, chg33, chg34, chg35, chg36

from ' + @schema + '.RCC_DeptCostChg_View) as r1

on p.mcare_id = r1.mcare_id and p.dkey = r1.dkey

join ' + @schema + '.charges c on p.mcare_id = c.mcare_id and p.dkey = c.dkey

join [dbo].facility f on f.mcare_id = p.mcare_id

where p.mcare_id = ''' + @facility + '''

and p.drg in (select drg from [dbo].drg_des where type = ''M'')

and c.charge_type = ''AMTREIM'') as d1

group by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

order by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

;'

print(@cmd+@cmd2);

exec (@cmd+@cmd2)

;

print 'finished ' + @facility;

END

FETCH NEXT FROM facility_cursor INTO @facility

END

CLOSE facility_cursor

DEALLOCATE facility_cursor

ASKER

This is the first SP that I run and the one that error's out??

It seems you have a join in the middle of your @cmd2, and the number of columns for the 2 selects do not match, they both have the same number of costs and chgs (36) but the first select has 22 columns at the beginig to only 2 columns on the second select.

ASKER

Here it is with the the 2@cmd- taken out -

I am still having the problems when I add those four columns-

I don't understand why when I add four coulmns to a create table- I have to add those columns in each stored procedure??

ALTER PROCEDURE [dbo].[populate_aprsev_icd9_medical]

@schema varchar(18)

AS

EXECUTE AS USER = @schema

;

DECLARE @cmd varchar(8000);

DECLARE @facility varchar(6);

IF (@schema = 'MEDPAR')

BEGIN

DECLARE facility_cursor CURSOR

FOR

SELECT mcare_id FROM dbo.facility

WHERE mcare_id in (SELECT distinct mcare_id FROM medpar.patient)

END

ELSE

BEGIN

DECLARE facility_cursor CURSOR

FOR

SELECT mcare_id FROM dbo.facility

WHERE mcare_id in (SELECT distinct mcare_id FROM ub92.patient)

END

OPEN facility_cursor

set nocount on

FETCH NEXT FROM facility_cursor INTO @facility

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

/*

(mcare_id,MDC,DRG,ICD9,aprdrg,aprsev_ind,aprsev_year,aprsev_qtr,type,Discharges,LOS,State,

AprMort,AprElos,AprEchg,ExpectedComplication,Complication,ActualComplication,ExpectedMortality,

Mortality,ActualMortality,outlier,afro_physician,afro_clinic,afro_snf,afro_hospital,afro_er,

afro_other,ppay_mcare,ppay_mcaid,ppay_bcbs,ppay_hmo,ppay_ppo,ppay_ogov,ppay_self,ppay_other,

disp_home,disp_snf,disp_exp,disp_hhs,disp_other,Reimbursement,

cost1,cost3,cost4,cost5,cost8,cost14,cost15,cost16,cost17,cost18,cost19,cost20,cost21,

cost22,cost23,cost24,cost25,cost29,cost30,cost31,cost32,cost33,cost34,cost35,cost36,

chg1,chg3,chg4,chg5,chg8,chg14,chg15,chg16,chg17,chg18,chg19,chg20,chg21,chg22,chg23,

chg24,chg25,chg29,chg30,chg31,chg32,chg33,chg34,chg35,chg36,created_date)

*/

SET @cmd = 'insert into ' + @schema + '.aprsev_icd9_summary

select mcare_id, mdc, drg, pdx as icd9, aprdrg, aprsev_ind, patient_year, patient_qtr, ''M'',

count(dkey) as discharges,

sum(los) as LOS,

max(state) as State,

sum(cast(aprmort_ind as int)) as AprMort,

sum(round(cast(aprdrg_elos as float),2)) as AprElos,

sum(round(cast(aprdrg_echg as float),2)) as AprEchg,

sum(cast(ecr02prob as float))/count(dkey) as ExpectedComplications,

sum(ecr02outc) as Complication,

sum(cast(ecr02outc as float))/count(dkey) as ActualComplication,

sum(cast(mortindex as float))/count(dkey) as ExpectedMortality,

sum(case disp when 20 then 1 else 0 end) as Mortality,

sum(case disp when 20 then 1 else 0 end)/ cast(count(dkey)as float) as ActualMortality,

sum(case when outsrc = 1 or outsrc = 2 then 1 else 0 end) as outlier,

sum(case afro when ''1'' then 1 else 0 end) as afro_physician,

sum(case afro when ''2'' then 1 else 0 end) as afro_clinic,

sum(case afro when ''3'' then 1 else 0 end) as afro_snf,

sum(case afro when ''4'' then 1 else 0 end) as afro_hospital,

sum(case afro when ''7'' then 1 else 0 end) as afro_er,

sum(case when afro = ''5'' or afro = ''6'' or afro = ''8'' or afro = ''9'' or afro = ''A'' or afro = ''B'' or afro = ''C'' then 1 else 0 end) as afro_other,

sum(case when ppay = ''MA'' or ppay = ''MD'' then 1 else 0 end) as ppay_mcare,

sum(case when ppay = ''MB'' or ppay = ''MC'' then 1 else 0 end) as ppay_mcaid,

sum(case when ppay = ''BC'' or ppay = ''CO'' then 1 else 0 end) as ppay_bcbs,

sum(case when ppay = ''HM'' then 1 else 0 end) as ppay_hmo,

sum(case when ppay = ''PP'' then 1 else 0 end) as ppay_ppo,

sum(case when ppay = ''CH'' or ppay = ''OG'' or ppay = ''WC'' then 1 else 0 end) as ppay_ogov,

sum(case when ppay = ''SP'' then 1 else 0 end) as ppay_self,

sum(case when ppay = ''OT'' or ppay = ''UK'' then 1 else 0 end) as ppay_other,

sum(case when disp = 1 then 1 else 0 end) as disp_home,

sum(case when disp = 3 then 1 else 0 end) as disp_snf,

sum(case when disp = 20 then 1 else 0 end) as disp_exp,

sum(case when disp = 6 then 1 else 0 end) as disp_hhs,

sum(case when disp <> 1 and disp <> 3 and disp <> 6 and disp <> 20 then 1 else 0 end) as disp_other,

sum(reimburse) as reimbursement,

sum(cost1) as cost1, sum(cost2) as cost2, sum(cost3) as cost3, sum(cost4) as cost4, sum(cost5) as cost5,sum(cost6) as cost6,

sum(cost7) as cost7,sum(cost8) as cost8,sum(cost9) as cost9,

sum(cost10) as cost10,sum(cost11) as cost11,sum(cost12) as cost12,

sum(cost13) as cost13,sum(cost14) as cost14,sum(cost15)as cost15,

sum(cost16) as cost16,sum(cost17) as cost17,sum(cost18) as cost18,

sum(cost19) as cost19,sum(cost20) as cost20,sum(cost21) as cost21,

sum(cost22) as cost22,sum(cost23) as cost23,sum(cost24) as cost24,

sum(cost25) as cost25,sum(cost26) as cost26,sum(cost27) as cost27,

sum(cost28) as cost28,sum(cost29) as cost29,sum(cost30) as cost30,

sum(cost31) as cost31,sum(cost32) as cost32,sum(cost33) as cost33,

sum(cost34) as cost34,sum(cost35) as cost35,sum(cost36) as cost36,

sum(chg1) as chg1,sum(chg2) as chg2,sum(chg3) as chg3,

sum(chg4) as chg4,sum(chg5) as chg5,sum(chg6) as chg6,

sum(chg7) as chg7,sum(chg8) as chg8,sum(chg9) as chg9,

sum(chg10) as chg10,sum(chg11) as chg11,sum(chg12) as chg12,

sum(chg13) as chg13,sum(chg14) as chg14,sum(chg15) as chg15,

sum(chg16) as chg16,sum(chg17) as chg17,sum(chg18) as chg18,

sum(chg19) as chg19,sum(chg20) as chg20,sum(chg21) as chg21,

sum(chg22) as chg22,sum(chg23) as chg23,sum(chg24) as chg24,

sum(chg25) as chg25,sum(chg26) as chg26,sum(chg27) as chg27,

sum(chg28) as chg28,sum(chg29) as chg29,sum(chg30) as chg30,

sum(chg31) as chg31,sum(chg32) as chg32,sum(chg33) as chg33,

sum(chg34) as chg34,sum(chg35) as chg35,sum(chg36) as chg36,

getdate()

from (

select p.mcare_id, p.dkey, p.mdc, p.drg, p.pdx, p.aprdrg, p.patient_year, p.patient_qtr, p.los,

f.state, p.outsrc, p.afro, p.ppay, p.aprsev_ind, p.aprmort_ind, p.aprdrg_elos, p.aprdrg_echg,

ecr02prob, ecr02outc, mortindex, disp, c.value as reimburse,

cost1,cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13,cost14,cost15,cost16,cost17,cost18,cost19,cost20,cost21,cost22,

cost23,cost24,cost25,cost26, cost27, cost28, cost29,cost30,cost31,cost32,cost33,cost34,cost35,cost36,

chg1,chg2,chg3,chg4,chg5,chg6, chg7, chg8,chg9, chg10, chg11, chg12, chg13, chg14,chg15,chg16,chg17,chg18,chg19,chg20,chg21,chg22,chg23,chg24,

chg25,chg26, chg27, chg28, chg29,chg30,chg31,chg32,chg33,chg34,chg35,chg36

from ' + @schema + '.patient p join (

select mcare_id, dkey, cost1, cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13, cost14, cost15, cost16, cost17, cost18, cost19, cost20, cost21, cost22, cost23,cost24,

cost25, cost26, cost27, cost28, cost29, cost30, cost31, cost32, cost33,

cost34, cost35, cost36, chg1, chg2, chg3, chg4, chg5, chg6, chg7, chg8, chg9, chg10, chg11, chg12, chg13, chg14, chg15, chg16, chg17, chg18, chg19, chg20, chg21, chg22, chg23, chg24, chg25, chg26, chg27, chg28, chg29, chg30, chg31, chg32, chg33, chg34, chg35, chg36

from ' + @schema + '.RCC_DeptCostChg_View) as r1

on p.mcare_id = r1.mcare_id and p.dkey = r1.dkey

join ' + @schema + '.charges c on p.mcare_id = c.mcare_id and p.dkey = c.dkey

join [dbo].facility f on f.mcare_id = p.mcare_id

where p.mcare_id = ''' + @facility + '''

and p.drg in (select drg from [dbo].drg_des where type = ''M'')

and c.charge_type = ''AMTREIM'') as d1

group by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

order by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

;'

print(@cmd)

;

exec (@cmd)

;

print 'finished ' + @facility;

END

FETCH NEXT FROM facility_cursor INTO @facility

END

CLOSE facility_cursor

DEALLOCATE facility_cursor

I am still having the problems when I add those four columns-

I don't understand why when I add four coulmns to a create table- I have to add those columns in each stored procedure??

ALTER PROCEDURE [dbo].[populate_aprsev_icd

@schema varchar(18)

AS

EXECUTE AS USER = @schema

;

DECLARE @cmd varchar(8000);

DECLARE @facility varchar(6);

IF (@schema = 'MEDPAR')

BEGIN

DECLARE facility_cursor CURSOR

FOR

SELECT mcare_id FROM dbo.facility

WHERE mcare_id in (SELECT distinct mcare_id FROM medpar.patient)

END

ELSE

BEGIN

DECLARE facility_cursor CURSOR

FOR

SELECT mcare_id FROM dbo.facility

WHERE mcare_id in (SELECT distinct mcare_id FROM ub92.patient)

END

OPEN facility_cursor

set nocount on

FETCH NEXT FROM facility_cursor INTO @facility

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

/*

(mcare_id,MDC,DRG,ICD9,apr

AprMort,AprElos,AprEchg,Ex

Mortality,ActualMortality,

afro_other,ppay_mcare,ppay

disp_home,disp_snf,disp_ex

cost1,cost3,cost4,cost5,co

cost22,cost23,cost24,cost2

chg1,chg3,chg4,chg5,chg8,c

chg24,chg25,chg29,chg30,ch

*/

SET @cmd = 'insert into ' + @schema + '.aprsev_icd9_summary

select mcare_id, mdc, drg, pdx as icd9, aprdrg, aprsev_ind, patient_year, patient_qtr, ''M'',

count(dkey) as discharges,

sum(los) as LOS,

max(state) as State,

sum(cast(aprmort_ind as int)) as AprMort,

sum(round(cast(aprdrg_elos

sum(round(cast(aprdrg_echg

sum(cast(ecr02prob as float))/count(dkey) as ExpectedComplications,

sum(ecr02outc) as Complication,

sum(cast(ecr02outc as float))/count(dkey) as ActualComplication,

sum(cast(mortindex as float))/count(dkey) as ExpectedMortality,

sum(case disp when 20 then 1 else 0 end) as Mortality,

sum(case disp when 20 then 1 else 0 end)/ cast(count(dkey)as float) as ActualMortality,

sum(case when outsrc = 1 or outsrc = 2 then 1 else 0 end) as outlier,

sum(case afro when ''1'' then 1 else 0 end) as afro_physician,

sum(case afro when ''2'' then 1 else 0 end) as afro_clinic,

sum(case afro when ''3'' then 1 else 0 end) as afro_snf,

sum(case afro when ''4'' then 1 else 0 end) as afro_hospital,

sum(case afro when ''7'' then 1 else 0 end) as afro_er,

sum(case when afro = ''5'' or afro = ''6'' or afro = ''8'' or afro = ''9'' or afro = ''A'' or afro = ''B'' or afro = ''C'' then 1 else 0 end) as afro_other,

sum(case when ppay = ''MA'' or ppay = ''MD'' then 1 else 0 end) as ppay_mcare,

sum(case when ppay = ''MB'' or ppay = ''MC'' then 1 else 0 end) as ppay_mcaid,

sum(case when ppay = ''BC'' or ppay = ''CO'' then 1 else 0 end) as ppay_bcbs,

sum(case when ppay = ''HM'' then 1 else 0 end) as ppay_hmo,

sum(case when ppay = ''PP'' then 1 else 0 end) as ppay_ppo,

sum(case when ppay = ''CH'' or ppay = ''OG'' or ppay = ''WC'' then 1 else 0 end) as ppay_ogov,

sum(case when ppay = ''SP'' then 1 else 0 end) as ppay_self,

sum(case when ppay = ''OT'' or ppay = ''UK'' then 1 else 0 end) as ppay_other,

sum(case when disp = 1 then 1 else 0 end) as disp_home,

sum(case when disp = 3 then 1 else 0 end) as disp_snf,

sum(case when disp = 20 then 1 else 0 end) as disp_exp,

sum(case when disp = 6 then 1 else 0 end) as disp_hhs,

sum(case when disp <> 1 and disp <> 3 and disp <> 6 and disp <> 20 then 1 else 0 end) as disp_other,

sum(reimburse) as reimbursement,

sum(cost1) as cost1, sum(cost2) as cost2, sum(cost3) as cost3, sum(cost4) as cost4, sum(cost5) as cost5,sum(cost6) as cost6,

sum(cost7) as cost7,sum(cost8) as cost8,sum(cost9) as cost9,

sum(cost10) as cost10,sum(cost11) as cost11,sum(cost12) as cost12,

sum(cost13) as cost13,sum(cost14) as cost14,sum(cost15)as cost15,

sum(cost16) as cost16,sum(cost17) as cost17,sum(cost18) as cost18,

sum(cost19) as cost19,sum(cost20) as cost20,sum(cost21) as cost21,

sum(cost22) as cost22,sum(cost23) as cost23,sum(cost24) as cost24,

sum(cost25) as cost25,sum(cost26) as cost26,sum(cost27) as cost27,

sum(cost28) as cost28,sum(cost29) as cost29,sum(cost30) as cost30,

sum(cost31) as cost31,sum(cost32) as cost32,sum(cost33) as cost33,

sum(cost34) as cost34,sum(cost35) as cost35,sum(cost36) as cost36,

sum(chg1) as chg1,sum(chg2) as chg2,sum(chg3) as chg3,

sum(chg4) as chg4,sum(chg5) as chg5,sum(chg6) as chg6,

sum(chg7) as chg7,sum(chg8) as chg8,sum(chg9) as chg9,

sum(chg10) as chg10,sum(chg11) as chg11,sum(chg12) as chg12,

sum(chg13) as chg13,sum(chg14) as chg14,sum(chg15) as chg15,

sum(chg16) as chg16,sum(chg17) as chg17,sum(chg18) as chg18,

sum(chg19) as chg19,sum(chg20) as chg20,sum(chg21) as chg21,

sum(chg22) as chg22,sum(chg23) as chg23,sum(chg24) as chg24,

sum(chg25) as chg25,sum(chg26) as chg26,sum(chg27) as chg27,

sum(chg28) as chg28,sum(chg29) as chg29,sum(chg30) as chg30,

sum(chg31) as chg31,sum(chg32) as chg32,sum(chg33) as chg33,

sum(chg34) as chg34,sum(chg35) as chg35,sum(chg36) as chg36,

getdate()

from (

select p.mcare_id, p.dkey, p.mdc, p.drg, p.pdx, p.aprdrg, p.patient_year, p.patient_qtr, p.los,

f.state, p.outsrc, p.afro, p.ppay, p.aprsev_ind, p.aprmort_ind, p.aprdrg_elos, p.aprdrg_echg,

ecr02prob, ecr02outc, mortindex, disp, c.value as reimburse,

cost1,cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13,cost14,cost15,cost1

cost23,cost24,cost25,cost2

chg1,chg2,chg3,chg4,chg5,c

chg25,chg26, chg27, chg28, chg29,chg30,chg31,chg32,ch

from ' + @schema + '.patient p join (

select mcare_id, dkey, cost1, cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13, cost14, cost15, cost16, cost17, cost18, cost19, cost20, cost21, cost22, cost23,cost24,

cost25, cost26, cost27, cost28, cost29, cost30, cost31, cost32, cost33,

cost34, cost35, cost36, chg1, chg2, chg3, chg4, chg5, chg6, chg7, chg8, chg9, chg10, chg11, chg12, chg13, chg14, chg15, chg16, chg17, chg18, chg19, chg20, chg21, chg22, chg23, chg24, chg25, chg26, chg27, chg28, chg29, chg30, chg31, chg32, chg33, chg34, chg35, chg36

from ' + @schema + '.RCC_DeptCostChg_View) as r1

on p.mcare_id = r1.mcare_id and p.dkey = r1.dkey

join ' + @schema + '.charges c on p.mcare_id = c.mcare_id and p.dkey = c.dkey

join [dbo].facility f on f.mcare_id = p.mcare_id

where p.mcare_id = ''' + @facility + '''

and p.drg in (select drg from [dbo].drg_des where type = ''M'')

and c.charge_type = ''AMTREIM'') as d1

group by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

order by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

;'

print(@cmd)

;

exec (@cmd)

;

print 'finished ' + @facility;

END

FETCH NEXT FROM facility_cursor INTO @facility

END

CLOSE facility_cursor

DEALLOCATE facility_cursor

ASKER

Okay -

Here is my code w/o the @cmd2.

But that still doesn't answer my question- do all of my stored procedures need to have all of the columns from the table?

SET @cmd = 'insert into ' + @schema + '.aprsev_icd9_summary

select mcare_id, mdc, drg, pdx as icd9, aprdrg, aprsev_ind, patient_year, patient_qtr, ''M'',

count(dkey) as discharges,

sum(los) as LOS,

max(state) as State,

sum(cast(aprmort_ind as int)) as AprMort,

sum(round(cast(aprdrg_elos as float),2)) as AprElos,

sum(round(cast(aprdrg_echg as float),2)) as AprEchg,

sum(cast(ecr02prob as float))/count(dkey) as ExpectedComplications,

sum(ecr02outc) as Complication,

sum(cast(ecr02outc as float))/count(dkey) as ActualComplication,

sum(cast(mortindex as float))/count(dkey) as ExpectedMortality,

sum(case disp when 20 then 1 else 0 end) as Mortality,

sum(case disp when 20 then 1 else 0 end)/ cast(count(dkey)as float) as ActualMortality,

sum(case when outsrc = 1 or outsrc = 2 then 1 else 0 end) as outlier,

sum(case afro when ''1'' then 1 else 0 end) as afro_physician,

sum(case afro when ''2'' then 1 else 0 end) as afro_clinic,

sum(case afro when ''3'' then 1 else 0 end) as afro_snf,

sum(case afro when ''4'' then 1 else 0 end) as afro_hospital,

sum(case afro when ''7'' then 1 else 0 end) as afro_er,

sum(case when afro = ''5'' or afro = ''6'' or afro = ''8'' or afro = ''9'' or afro = ''A'' or afro = ''B'' or afro = ''C'' then 1 else 0 end) as afro_other,

sum(case when ppay = ''MA'' or ppay = ''MD'' then 1 else 0 end) as ppay_mcare,

sum(case when ppay = ''MB'' or ppay = ''MC'' then 1 else 0 end) as ppay_mcaid,

sum(case when ppay = ''BC'' or ppay = ''CO'' then 1 else 0 end) as ppay_bcbs,

sum(case when ppay = ''HM'' then 1 else 0 end) as ppay_hmo,

sum(case when ppay = ''PP'' then 1 else 0 end) as ppay_ppo,

sum(case when ppay = ''CH'' or ppay = ''OG'' or ppay = ''WC'' then 1 else 0 end) as ppay_ogov,

sum(case when ppay = ''SP'' then 1 else 0 end) as ppay_self,

sum(case when ppay = ''OT'' or ppay = ''UK'' then 1 else 0 end) as ppay_other,

sum(case when disp = 1 then 1 else 0 end) as disp_home,

sum(case when disp = 3 then 1 else 0 end) as disp_snf,

sum(case when disp = 20 then 1 else 0 end) as disp_exp,

sum(case when disp = 6 then 1 else 0 end) as disp_hhs,

sum(case when disp <> 1 and disp <> 3 and disp <> 6 and disp <> 20 then 1 else 0 end) as disp_other,

sum(reimburse) as reimbursement,

sum(cost1) as cost1, sum(cost2) as cost2, sum(cost3) as cost3, sum(cost4) as cost4, sum(cost5) as cost5,sum(cost6) as cost6,

sum(cost7) as cost7,sum(cost8) as cost8,sum(cost9) as cost9,

sum(cost10) as cost10,sum(cost11) as cost11,sum(cost12) as cost12,

sum(cost13) as cost13,sum(cost14) as cost14,sum(cost15)as cost15,

sum(cost16) as cost16,sum(cost17) as cost17,sum(cost18) as cost18,

sum(cost19) as cost19,sum(cost20) as cost20,sum(cost21) as cost21,

sum(cost22) as cost22,sum(cost23) as cost23,sum(cost24) as cost24,

sum(cost25) as cost25,sum(cost26) as cost26,sum(cost27) as cost27,

sum(cost28) as cost28,sum(cost29) as cost29,sum(cost30) as cost30,

sum(cost31) as cost31,sum(cost32) as cost32,sum(cost33) as cost33,

sum(cost34) as cost34,sum(cost35) as cost35,sum(cost36) as cost36,

sum(chg1) as chg1,sum(chg2) as chg2,sum(chg3) as chg3,

sum(chg4) as chg4,sum(chg5) as chg5,sum(chg6) as chg6,

sum(chg7) as chg7,sum(chg8) as chg8,sum(chg9) as chg9,

sum(chg10) as chg10,sum(chg11) as chg11,sum(chg12) as chg12,

sum(chg13) as chg13,sum(chg14) as chg14,sum(chg15) as chg15,

sum(chg16) as chg16,sum(chg17) as chg17,sum(chg18) as chg18,

sum(chg19) as chg19,sum(chg20) as chg20,sum(chg21) as chg21,

sum(chg22) as chg22,sum(chg23) as chg23,sum(chg24) as chg24,

sum(chg25) as chg25,sum(chg26) as chg26,sum(chg27) as chg27,

sum(chg28) as chg28,sum(chg29) as chg29,sum(chg30) as chg30,

sum(chg31) as chg31,sum(chg32) as chg32,sum(chg33) as chg33,

sum(chg34) as chg34,sum(chg35) as chg35,sum(chg36) as chg36,

getdate()

from (

select p.mcare_id, p.dkey, p.mdc, p.drg, p.pdx, p.aprdrg, p.patient_year, p.patient_qtr, p.los,

f.state, p.outsrc, p.afro, p.ppay, p.aprsev_ind, p.aprmort_ind, p.aprdrg_elos, p.aprdrg_echg,

ecr02prob, ecr02outc, mortindex, disp, c.value as reimburse,

cost1,cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13,cost14,cost15,cost16,cost17,cost18,cost19,cost20,cost21,cost22,

cost23,cost24,cost25,cost26, cost27, cost28, cost29,cost30,cost31,cost32,cost33,cost34,cost35,cost36,

chg1,chg2,chg3,chg4,chg5,chg6, chg7, chg8,chg9, chg10, chg11, chg12, chg13, chg14,chg15,chg16,chg17,chg18,chg19,chg20,chg21,chg22,chg23,chg24,

chg25,chg26, chg27, chg28, chg29,chg30,chg31,chg32,chg33,chg34,chg35,chg36

from ' + @schema + '.patient p join (

select mcare_id, dkey, cost1, cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13, cost14, cost15, cost16, cost17, cost18, cost19, cost20, cost21, cost22, cost23,cost24,

cost25, cost26, cost27, cost28, cost29, cost30, cost31, cost32, cost33,

cost34, cost35, cost36, chg1, chg2, chg3, chg4, chg5, chg6, chg7, chg8, chg9, chg10, chg11, chg12, chg13, chg14, chg15, chg16, chg17, chg18, chg19, chg20, chg21, chg22, chg23, chg24, chg25, chg26, chg27, chg28, chg29, chg30, chg31, chg32, chg33, chg34, chg35, chg36

from ' + @schema + '.RCC_DeptCostChg_View) as r1

on p.mcare_id = r1.mcare_id and p.dkey = r1.dkey

join ' + @schema + '.charges c on p.mcare_id = c.mcare_id and p.dkey = c.dkey

join [dbo].facility f on f.mcare_id = p.mcare_id

where p.mcare_id = ''' + @facility + '''

and p.drg in (select drg from [dbo].drg_des where type = ''M'')

and c.charge_type = ''AMTREIM'') as d1

group by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

order by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

;'

print(@cmd)

;

exec (@cmd)

;

print 'finished ' + @facility;

END

FETCH NEXT FROM facility_cursor INTO @facility

END

CLOSE facility_cursor

DEALLOCATE facility_cursor

Here is my code w/o the @cmd2.

But that still doesn't answer my question- do all of my stored procedures need to have all of the columns from the table?

SET @cmd = 'insert into ' + @schema + '.aprsev_icd9_summary

select mcare_id, mdc, drg, pdx as icd9, aprdrg, aprsev_ind, patient_year, patient_qtr, ''M'',

count(dkey) as discharges,

sum(los) as LOS,

max(state) as State,

sum(cast(aprmort_ind as int)) as AprMort,

sum(round(cast(aprdrg_elos

sum(round(cast(aprdrg_echg

sum(cast(ecr02prob as float))/count(dkey) as ExpectedComplications,

sum(ecr02outc) as Complication,

sum(cast(ecr02outc as float))/count(dkey) as ActualComplication,

sum(cast(mortindex as float))/count(dkey) as ExpectedMortality,

sum(case disp when 20 then 1 else 0 end) as Mortality,

sum(case disp when 20 then 1 else 0 end)/ cast(count(dkey)as float) as ActualMortality,

sum(case when outsrc = 1 or outsrc = 2 then 1 else 0 end) as outlier,

sum(case afro when ''1'' then 1 else 0 end) as afro_physician,

sum(case afro when ''2'' then 1 else 0 end) as afro_clinic,

sum(case afro when ''3'' then 1 else 0 end) as afro_snf,

sum(case afro when ''4'' then 1 else 0 end) as afro_hospital,

sum(case afro when ''7'' then 1 else 0 end) as afro_er,

sum(case when afro = ''5'' or afro = ''6'' or afro = ''8'' or afro = ''9'' or afro = ''A'' or afro = ''B'' or afro = ''C'' then 1 else 0 end) as afro_other,

sum(case when ppay = ''MA'' or ppay = ''MD'' then 1 else 0 end) as ppay_mcare,

sum(case when ppay = ''MB'' or ppay = ''MC'' then 1 else 0 end) as ppay_mcaid,

sum(case when ppay = ''BC'' or ppay = ''CO'' then 1 else 0 end) as ppay_bcbs,

sum(case when ppay = ''HM'' then 1 else 0 end) as ppay_hmo,

sum(case when ppay = ''PP'' then 1 else 0 end) as ppay_ppo,

sum(case when ppay = ''CH'' or ppay = ''OG'' or ppay = ''WC'' then 1 else 0 end) as ppay_ogov,

sum(case when ppay = ''SP'' then 1 else 0 end) as ppay_self,

sum(case when ppay = ''OT'' or ppay = ''UK'' then 1 else 0 end) as ppay_other,

sum(case when disp = 1 then 1 else 0 end) as disp_home,

sum(case when disp = 3 then 1 else 0 end) as disp_snf,

sum(case when disp = 20 then 1 else 0 end) as disp_exp,

sum(case when disp = 6 then 1 else 0 end) as disp_hhs,

sum(case when disp <> 1 and disp <> 3 and disp <> 6 and disp <> 20 then 1 else 0 end) as disp_other,

sum(reimburse) as reimbursement,

sum(cost1) as cost1, sum(cost2) as cost2, sum(cost3) as cost3, sum(cost4) as cost4, sum(cost5) as cost5,sum(cost6) as cost6,

sum(cost7) as cost7,sum(cost8) as cost8,sum(cost9) as cost9,

sum(cost10) as cost10,sum(cost11) as cost11,sum(cost12) as cost12,

sum(cost13) as cost13,sum(cost14) as cost14,sum(cost15)as cost15,

sum(cost16) as cost16,sum(cost17) as cost17,sum(cost18) as cost18,

sum(cost19) as cost19,sum(cost20) as cost20,sum(cost21) as cost21,

sum(cost22) as cost22,sum(cost23) as cost23,sum(cost24) as cost24,

sum(cost25) as cost25,sum(cost26) as cost26,sum(cost27) as cost27,

sum(cost28) as cost28,sum(cost29) as cost29,sum(cost30) as cost30,

sum(cost31) as cost31,sum(cost32) as cost32,sum(cost33) as cost33,

sum(cost34) as cost34,sum(cost35) as cost35,sum(cost36) as cost36,

sum(chg1) as chg1,sum(chg2) as chg2,sum(chg3) as chg3,

sum(chg4) as chg4,sum(chg5) as chg5,sum(chg6) as chg6,

sum(chg7) as chg7,sum(chg8) as chg8,sum(chg9) as chg9,

sum(chg10) as chg10,sum(chg11) as chg11,sum(chg12) as chg12,

sum(chg13) as chg13,sum(chg14) as chg14,sum(chg15) as chg15,

sum(chg16) as chg16,sum(chg17) as chg17,sum(chg18) as chg18,

sum(chg19) as chg19,sum(chg20) as chg20,sum(chg21) as chg21,

sum(chg22) as chg22,sum(chg23) as chg23,sum(chg24) as chg24,

sum(chg25) as chg25,sum(chg26) as chg26,sum(chg27) as chg27,

sum(chg28) as chg28,sum(chg29) as chg29,sum(chg30) as chg30,

sum(chg31) as chg31,sum(chg32) as chg32,sum(chg33) as chg33,

sum(chg34) as chg34,sum(chg35) as chg35,sum(chg36) as chg36,

getdate()

from (

select p.mcare_id, p.dkey, p.mdc, p.drg, p.pdx, p.aprdrg, p.patient_year, p.patient_qtr, p.los,

f.state, p.outsrc, p.afro, p.ppay, p.aprsev_ind, p.aprmort_ind, p.aprdrg_elos, p.aprdrg_echg,

ecr02prob, ecr02outc, mortindex, disp, c.value as reimburse,

cost1,cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13,cost14,cost15,cost1

cost23,cost24,cost25,cost2

chg1,chg2,chg3,chg4,chg5,c

chg25,chg26, chg27, chg28, chg29,chg30,chg31,chg32,ch

from ' + @schema + '.patient p join (

select mcare_id, dkey, cost1, cost2, cost3, cost4, cost5, cost6, cost7, cost8, cost9, cost10, cost11, cost12, cost13, cost14, cost15, cost16, cost17, cost18, cost19, cost20, cost21, cost22, cost23,cost24,

cost25, cost26, cost27, cost28, cost29, cost30, cost31, cost32, cost33,

cost34, cost35, cost36, chg1, chg2, chg3, chg4, chg5, chg6, chg7, chg8, chg9, chg10, chg11, chg12, chg13, chg14, chg15, chg16, chg17, chg18, chg19, chg20, chg21, chg22, chg23, chg24, chg25, chg26, chg27, chg28, chg29, chg30, chg31, chg32, chg33, chg34, chg35, chg36

from ' + @schema + '.RCC_DeptCostChg_View) as r1

on p.mcare_id = r1.mcare_id and p.dkey = r1.dkey

join ' + @schema + '.charges c on p.mcare_id = c.mcare_id and p.dkey = c.dkey

join [dbo].facility f on f.mcare_id = p.mcare_id

where p.mcare_id = ''' + @facility + '''

and p.drg in (select drg from [dbo].drg_des where type = ''M'')

and c.charge_type = ''AMTREIM'') as d1

group by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

order by mcare_id, drg, mdc, pdx, aprdrg, aprsev_ind, patient_year, patient_qtr

;'

print(@cmd)

;

exec (@cmd)

;

print 'finished ' + @facility;

END

FETCH NEXT FROM facility_cursor INTO @facility

END

CLOSE facility_cursor

DEALLOCATE facility_cursor

ASKER

Not Null as phys_high,

Not Null as phys_low,

Not Null as Los_high,

Not Null as Los_low,

If I need to add columns can I add them as this??

Not Null as phys_low,

Not Null as Los_high,

Not Null as Los_low,

If I need to add columns can I add them as this??

Sorry, for a secomd I dont know why I thoughtthe Join is the union hence my column count column,

if you dont want to redefine the tables everytime

you should be able to use

insert into table (col1, col2, col3)

values(@col1, @col2, @col3)

but that will enter null or whatever default you have set for the new columns

if you dont want to redefine the tables everytime

you should be able to use

insert into table (col1, col2, col3)

values(@col1, @col2, @col3)

but that will enter null or whatever default you have set for the new columns

ASKER

So when I enter new columns on the create table and I populate that table with a SP- I need to have EVERY column in the SP- that is what you are saying right??

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

That worked! Thank YOU!!

SELECT * FROM TABLE

instead of

SELECT col1, col2, col3 ... FROM TABLE

resp:

INSERT INTO TABLENAME

instead of

INSERT INTO TABLENAME ( col1, col2, col3 ... )

if you do NOT do that, you have to recompile the procedure after changing the tables anyhow.