"For some reason," writes Graeme Hefner, "someone decided that it'd be best that we write our own bug/issue tracking system instead of using one of the plethora of fully-functional systems available on the market."

"For some reason, someone — perhaps the same someone — decided that the best way to develop this system was a SQL stored procedure called by a Classic ASP page that simply dumped everything to a single page list. Every ticket, ever. This is the core of its entire logic, which impressively uses of SQL, HTML, and JavaScript all in one query."

ALTER PROCEDURE [sp_Tickets_Select_Main]
  @StatusID int,
  @UserFilter varchar(255)  = NULL,
  @DateFilter varchar(255)  = NULL,
  @PriorityFilter varchar(255)  = NULL,
  @CategoryFilter varchar(255)  = NULL,
  @TechFilter varchar(255) = NULL,
  @DescriptionFilter varchar(255) = NULL
AS

SELECT 
   '<input type="button" class="p' + cast(Tickets.PriorityID as varchar) 
   + '" onmouseover="hov(this,''hov p' + cast(Tickets.PriorityID as varchar) 
   + ''')" onmouseout="hov(this,''p' 
   + cast(Tickets.PriorityID as varchar) 
   + ''')" onclick="st('+ cast(Tickets.TicketID as varchar) 
   + ')" value="#' + cast(Tickets.TicketID as varchar) + '">' ,
   Category.CategoryName,
   UserInfo.UserFullName,
   (left(Tickets.Description,512) + '...'),
   Tickets.IssueDate
	

FROM 
   Tickets 
   INNER JOIN Status
      ON Tickets.StatusID = Status.StatusID
   INNER JOIN  Tech
      ON Tickets.TechID = Tech.TechID
   INNER JOIN Category
      ON Tickets.CategoryID = Category.CategoryID
   INNER JOIN Priority
      ON Tickets.PriorityID = Priority.PriorityID
   INNER JOIN UserInfo
      ON Tickets.UserName = UserInfo.UserName

WHERE 
   Tickets.StatusID = @StatusID
   AND (UserInfo.UserFullName LIKE  '%' + ISNULL(@UserFilter, UserInfo.UserFullName)  + '%' 
        OR Tickets.UserName LIKE  '%' + ISNULL(@UserFilter, Tickets.UserName)+ '%' 
	OR UserInfo.UserExt LIKE '%' + ISNULL(@UserFilter, UserInfo.UserExt)+ '%' )
   AND convert(varchar, Tickets.IssueDate,101) LIKE 
          '%' + ISNULL(@DateFilter, convert(varchar, Tickets.IssueDate,101)) + '%'
   AND (Tickets.PriorityID LIKE '%' + ISNULL(@PriorityFilter, Tickets.PriorityID) + '%' 
        OR Priority.PriorityName LIKE '%' + ISNULL(@PriorityFilter, Priority.PriorityName) + '%')
   AND (Category.CategoryName LIKE '%' + ISNULL(@CategoryFilter, Category.CategoryName) + '%' 
        OR Category.CategoryName LIKE '%' + ISNULL(@CategoryFilter, Category.CategoryName) + '%')
   AND (Tickets.TechID LIKE '%' + ISNULL(@TechFilter, Tickets.TechID) + '%' 
        OR Tech.TechUserName LIKE '%' + ISNULL(@TechFilter,Tech.TechUserName) + '%')
   AND Tickets.Description LIKE '%' + ISNULL(@DescriptionFilter, Tickets.Description) + '%'

ORDER BY 
   Tickets.PriorityID DESC,
   Tickets.TicketID DESC
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!