1. Home
  2. Categories
  3. Side Bar WTF
  4. What if we need more than 17 parameters?

What if we need more than 17 parameters?

  • B

    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.

    0
  • configurator

    @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.

    0
  • mikeTheLiar

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

    0
  • B

    @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.

    0
  • dhromed

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

    0
  • error
    Considered Harmful

    @dhromed said:

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

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

    0
  • ?

    @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.

    0
  • ekolis

    @mikeTheLiar said:

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

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

    0
  • blakeyrat

    @mikeTheLiar said:

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

    You... you're real!?

    0
  • mikeTheLiar

    @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.

    0
  • mikeTheLiar

    @ekolis said:

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

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

    0
  • RTapeLoadingError

    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

    0
Log in to reply