jwcolby
jwcolby at colbyconsulting.com
Sat Oct 16 07:35:49 CDT 2010
I am working on code to merge two records into one and update the merged record back into sql server. Visual studio builds crud statements automatically behind the scenes. The following is (part of) the update crud statement. This is for the database from hell with ~580 fields. AFAICT VS builds up a parameter for each field, then fills those parameters, then passes the lot back to SQL Server. Can you say Ick? Anyway, I thought I'd let you see what robo-code looks like behind the scenes. I clipped it down severely because the whole thing was too big to get into our forum email limits. There was SEVENTY SEVEN THOUSAND + characters in the original SQL statement. SEVENTY SEVEN THOUSAND. UPDATE [_DataHSIDMergePurge].[dbo].[tblHSID] SET [HashPerson] = @p1, [PKID] = @p2, [UpdCount] = @p3, [FieldsUpd] = @p4, [MergedRec] = @p5, [Household_Occupation_code] = @p6, [Presence_of_Adults_Unknown_Age] = @p7, [Presence_of_adults_age_75_specific] = @p8, [Presence_of_adults_age_65_74_specific] = @p9, [Presence_of_adults_age_55_64_specific] = @p10, [Presence_of_adults_age_45_54_specific] = @p11, [Presence_of_adults_age_35_44_specific] = @p12, [Presence_of_adults_age_25_34_specific] = @p13, [Presence_of_adults_age_18_24_specific] = @p14, [Presence_of_children_unknown_gender_00_17] = @p15, [Presence_of_Children_Age_00_02] = @p16, [Presence_of_Children_Age_03_05] = @p17, [Presence_of_Children_Age_06_10] = @p18, [Presence_of_Children_Age11_15] = @p19, [Presence_of_Children_Age_16_17] = @p20, [Number_of_Persons] = @p21, [Number_of_Children] = @p22, [Family_composition] = @p23, [Marital_Status] = @p24, [Match_File_Indicator] = @p25, [Household_Age_Indicator] = @p26, [Household_Age_Code] = @p27, [Number_of_adults_in_household] = @p28, [Narrow_Income_Band] = @p29, [Speaks_Spanish_at_Home_Self_Reported] = @p30, [SOHO_Indicator] = @p31, [Expand_Initial_to_Name_Match_Indicator] = @p32, [Second_Name_of_Opposite_Gender_Match_Indicator] = @p33, [Middle_Initial] = @p34, [Surname_Suffix] = @p35, [Gender_code] = @p36, [Title_code] = @p37, [Marital_Status_code] = @p38, [Age_In_2_Yr_Ranges] = @p39, [Member_code_of_person] = @p40, [Occupation_code_of_person] = @p41, [Verification_date_of_person] = @p42, [Given_name_of_person] = @p43, [Responder_Education] = @p44, [Spouse_Occupation] = @p45, [Spouse_Education] = @p46, [Spouse_Gender] = @p47, [Middle_Initial_for_opposite_gender] = @p48, Another piece: [Pct_SOME_OTHER_RACE_HOUSEHOLDER] IS NULL) OR ([Pct_SOME_OTHER_RACE_HOUSEHOLDER] = @p1489)) AND ((@p1490 = 1 AND [Pct_2_RACE_HOUSEHOLDER] IS NULL) OR ([Pct_2_RACE_HOUSEHOLDER] = @p1491)) AND ((@p1492 = 1 AND [Pct_HISPANIC_or_LATINO_HOUSEHOLDER] IS NULL) OR ([Pct_HISPANIC_or_LATINO_HOUSEHOLDER] = @p1493)) AND ((@p1494 = 1 AND [Pct_HISPANIC_U15] IS NULL) OR ([Pct_HISPANIC_U15] = @p1495)) AND ((@p1496 = 1 AND [Pct_HISPANIC_15_59_YEARS_OF_AGE] IS NULL) OR ([Pct_HISPANIC_15_59_YEARS_OF_AGE] = @p1497)) AND ((@p1498 = 1 AND [Pct_HISPANIC_60_Plus] IS NULL) OR ([Pct_HISPANIC_60_Plus] = @p1499)) AND ((@p1500 = 1 AND [DMA_CODE] IS NULL) OR ([DMA_CODE] = @p1501)) AND ((@p1502 = 1 AND [MSA_CODE] IS NULL) OR ([MSA_CODE] = @p1503)) AND ((@p1504 = 1 AND [Heartburn_Acid_Indigestion_in_HH] IS NULL) OR ([Heartburn_Acid_Indigestion_in_HH] = @p1505)) AND ((@p1506 = 1 AND [HH_ACNE] IS John W. Colby www.ColbyConsulting.com