Michael Maddison
michael at ddisolutions.com.au
Sun Oct 17 17:29:39 CDT 2010
Yikes! At least you don't have to write it out yourself J Is there a limit to SQL statement size? I would prefer the parameter version for 'normal' work, less issues with data. Is there a difference in performance? Cheers Michael M From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Saturday, 16 October 2010 11:36 PM To: VBA Subject: [dba-VB] Visual studio auto-generated CRUD 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 _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com ________________________________ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1136 / Virus Database: 422/3203 - Release Date: 10/17/10