In what way is classic ASP classic?

An old friend tagged me for a quick gig last night.  They have an intranet that is written in classic ASP.   They needed some reporting changes.  Specifically to add a "Employees" column to a set of reports.

Easy enough, right?  The catch is that there can be more than one employee per case.

The solution was to put together a SQL function to return a comma separated list of Employees

CREATE function [dbo].[GetEmployeesByCaseId] (@caseid int)
returns varchar(1000)
as
begin
--GetEmployeesByCaseId
--I return a comma separated list of Employees assigned to a case.
--USAGE select dbo.GetEmployeesByCaseId(18715)
--Created By Elizabeth Greene 8 October 2013
--Elizabeth.a.greene@gmail.com  615-280-0830

declare @EmployeeNames Varchar(1000)
select @EmployeeNames = 
   COALESCE(@EmployeeNames + ', ', '') +    
   COALESCE(employee_name_First,'') + ' ' +
   COALESCE(EMPLOYEE_NAME_LAST,'')
   from EmployeeAssignment
   inner join Employee
     on EmployeeId= EmployeeAssignment_EmployeeId
   where
     employeeAssignment.EmployeeAssignment_caseid = @caseid
  
 return @EmployeeNames
end

Then insert that function in the sql query that made the report

sql = "SELECT CaseID, Case_FileNumber, ClientID, SubjectID, Status_Name, client_name, Company_Name, Subject_State, Subject_FirstName, Subject_LastName, Subject_MiddleName, Case_DateStart, Case_DateEnd, Case_DateReopened ,dbo.GetEmployeesByCaseId(CaseId) as EmployeeName FROM [CaseListing] WHERE " ...

And finally add the column to the report

<td>
 <%=search_data(FieldID(search_field_array,"EmployeeName"),search_rowcounter)%>
&nbsp;</td>

All done.

I kind of miss classic ASP; in the same way I miss my '72 Impala.  It would be nice to have it back, but only if  I didn't have to drive it.  :)

This blog is kind of plain.  I should fix that.