Link to home
Start Free TrialLog in
Avatar of smoschkau
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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

this message is usually when you have
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.
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
Avatar of smoschkau
smoschkau

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
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.
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
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
           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??
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
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
Avatar of hoda007
hoda007

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That worked! Thank YOU!!