[dba-VB] Visual studio auto-generated CRUD

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



More information about the dba-VB mailing list