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 DNCFROM 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!
-
@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:
18, chai. The program should be lucky.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.
-
@mikeTheLiar said:
Pfft. Everybody knows there's no such thing as 18.
Oh, so you're one of those pedophiles, then?
-
-
@blakeyrat said:
@mikeTheLiar said:
Pfft. Everybody knows there's no such thing as 18.
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