What if we need more than 17 parameters?



  • I just started new employment, and there is a legacy VB6 system wired up to an ASP.Net frontend via a bunch of COM objects (I'll write up at some point the pipeline that takes place through, it involves lots of database queries and XSLT).

    I'm digging through this code, trying to make heads or tails of everything, working on a few bug reports as I go, and I stumble across this:

    Select Case i - 1
    	Case 0
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0))
    	Case 1
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1))
    	Case 2
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2))
    	Case 3
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3))
    	Case 4
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4))
    	Case 5
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5))
    	Case 6
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6))
    	Case 7
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7))
    	Case 8
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8))
    	Case 9
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8), vValArray(9))
    	Case 10
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8), vValArray(9), vValArray(10))
    	Case 11
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8), vValArray(9), vValArray(10), vValArray(11))
    	Case 12
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8), vValArray(9), vValArray(10), vValArray(11), vValArray(12))
    	Case 13
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8), vValArray(9), vValArray(10), vValArray(11), vValArray(12), vValArray(13))
    	Case 14
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8), vValArray(9), vValArray(10), vValArray(11), vValArray(12), vValArray(13), vValArray(14))
    	Case 15
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8), vValArray(9), vValArray(10), vValArray(11), vValArray(12), vValArray(13), vValArray(14), vValArray(15))
    	Case 16
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8), vValArray(9), vValArray(10), vValArray(11), vValArray(12), vValArray(13), vValArray(14), vValArray(15), vValArray(16))
    	Case 17
    		Set rsRows = oReportBO.RunReport(oReportBO.IDY, sSrv, sDb, vValArray(0), vValArray(1), vValArray(2), vValArray(3), vValArray(4), vValArray(5), vValArray(6), vValArray(7), vValArray(8), vValArray(9), vValArray(10), vValArray(11), vValArray(12), vValArray(13), vValArray(14), vValArray(15), vValArray(16), vValArray(17))
    End Select
    

    As an added bonus, the error message that lead me to this area of code is rendered out with:

    ErrorHandler:
        soutSelections = "<SCRIPT language=javascript runat=client>window.top.showError('Report Object Failure: " & Replace(Err.Description, "'", vbNullString) & "')</SCRIPT>"
    

    I have some fun times ahead of me.



  • @bardofspoons42 said:

    What if we need more than 17 parameters?

    I wouldn't worry about it. This code clearly supports even as many as 18 parameters! You're safe for the foreseeable future.



  • Pfft. Everybody knows there's no such thing as 18.



  • @configurator said:

    @bardofspoons42 said:
    What if we need more than 17 parameters?

    I wouldn't worry about it. This code clearly supports even as many as 18 parameters! You're safe for the foreseeable future.

    I actually noticed that right after I posted. But didn't feel like fixing it

    In the time since the first post, I've gone further down the rabbit hole, and I have a stored proc like this staring me in the face:

    SELECT
            cast(Month(p.BirthDate) as varchar) + '/' + cast(day(p.Birthdate) as varchar)
    		as BirthMonthDay,
    	'(Prospect)' as Relation,
    	IsNull(p.LastName,'') + IsNull(', ' + p.FirstName,'') as [Name],
    	p.BirthDate as DateOfBirth,
    	(case when GetDate() < cast(Month(p.Birthdate)as varchar) + '/' +
    			(case when cast(Month(p.Birthdate)as varchar) + '/' +
    				cast(Day(p.Birthdate)as varchar) = '2/29'
    			then '28'
    			else cast(Day(p.Birthdate)as varchar)
    			end) + '/' +
    			cast(Year(GetDate()) as varchar)
    		then DateDiff(yyyy, p.Birthdate, GetDate()) - 1
    		else DateDiff(yyyy, p.Birthdate, GetDate())
    		end)
    		as Age,
    	(case when a.Address1 is Null
    	 	then '' else a.Address1 end) +
    	 (case when a.Address2 is Null
    	 	then '' else '
    ' + a.Address2 end) + (case when a.City is Null or a.City = ' ' then '' else '
    ' + a.City + ', ' end) + (case when a.State is Null then '' else a.State end) + ' ' + (case when a.ZipCode is Null then '' else a.ZipCode end) as Address, ph1.PhoneNumber + case when len(rtrim(ph1.Extension)) > 1 then ' ext: ' + ph1.Extension else '' end as Phone, p.GenderUL as Gender, [Apartment].[ApartmentNumber] AS UnitNo, Month(p.BirthDate) as bMonth, Day(p.BirthDate) as bDay, dbo.fn_GetDNCForPhone (ph1.NationalDNCFlag, ph1.FirstInqDiff, ph1.LastTranDiff, ph1.CompDNCDiff, ph1.CompanyDNCDate, ph1.WrittenPermissionDate) as DNC

    FROM Prospect pr
    LEFT JOIN
    Resident r ON pr.ProspectIDY = r.ProspectIDY
    LEFT JOIN
    [Apartment] ON r.[ApartmentFK] = [Apartment].[ApartmentIDY]
    INNER JOIN
    People p ON pr.peopleidy = p.peopleidy
    LEFT JOIN
    PeopleAddress pa ON p.PeopleIDY = pa.PeopleIDY
    INNER JOIN
    Address a ON pa.AddressIDY = a.AddressIDY and
    a.IsPrimary = 1
    LEFT JOIN
    Phone ph1 ON a.AddressIDY = ph1.AddressIDY and
    ph1.DisplayOrder = 1
    LEFT JOIN
    [User] u ON pr.SalesCounselorUserIDY = u.UserIDY
    LEFT JOIN
    ProspectStatus ps ON pr.ProspectIDY = ps.ProspectIDY and
    ps.DateExpired is null
    LEFT JOIN
    PickListValues plv ON ps.StatusPL = plv.PickListValueIDY
    WHERE (@IncPeople <> 'Spouses Only') AND
    (pr.DateExpired IS NULL) AND
    (pr.CommunityIDY = @CommunityIDY) AND
    (Month(p.BirthDate) BETWEEN @BegMonth AND @EndMonth) AND
    (@Option IS NULL OR (pr.ProspectIDY IN (SELECT * FROM #Filter)))

    Formatting text in SQL? Check. UDF that's called for every row? Check. This just keeps getting better. Oh, that #Filter temp table? It's populated from a few UDFs that build up SQL statements on the fly, and then are EXEC'd.



  •  Oh god the smell! The putrid half-digested stench!


  • Winner of the 2016 Presidential Election

    @dhromed said:

     Oh god the smell! The putrid half-digested stench!

    Sorry, that happens whenever I eat foods sweetened with maltitol.



  • @configurator said:

    @bardofspoons42 said:
    What if we need more than 17 parameters?

    I wouldn't worry about it. This code clearly supports even as many as 18 parameters! You're safe for the foreseeable future.
    18, chai. The program should be lucky.



  • @mikeTheLiar said:

    Pfft. Everybody knows there's no such thing as 18.

    Oh, so you're one of those pedophiles, then?



  • @mikeTheLiar said:

    Pfft. Everybody knows there's no such thing as 18.

    You... you're real!?



  • @blakeyrat said:

    @mikeTheLiar said:
    Pfft. Everybody knows there's no such thing as 18.

    You... you're real!?

    Mike knows much, tells some. Sadly, you're the first person other than my girlfriend who got that reference.



  • @ekolis said:

    @mikeTheLiar said:
    Pfft. Everybody knows there's no such thing as 18.

    Oh, so you're one of those pedophiles, then?



  • That SQL is pretty horrible to look at.

    I particularly like how the author seems to know how to use IsNull at the start of the SP... @Stored Procedure said:

    IsNull(p.LastName,'')

    ...but forgets a few lines later... @Stored Procedure said:

    case when a.ZipCode is Null
    	 	then '' else a.ZipCode  end


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.