Toss Your Cookies Round 'n' Round

Today's post is a revisit of a classic, Tossing Your Cookies.

As you read this short introduction, I'm guessing that you can see today's code in your peripheral vision and are wondering, Tossing Your Cookies? What does that have to do with a huge, ugly SQL statement? I'll let the combination of "Cookies" and "SQL" sink in for a moment ........ ahh, there it is. And yes, it's exactly what your thinking. It's from the system that Chris F inherited and is used to redirect the browser to a results page, execute the "SQL cookie" (as the sa user, of course), and display the results in a grid.

sql = _
  "select cq.InquiryID, cq.userid, cq.assignedto, cq.customerid, cq.InquiryDate, 
     cq.InquiryType, cq.Priority, " & _
  "isnull((select ct.typename from CustomerDB.dbo.InquiryTypes ct where ct.typeid 

     = isnull(cq.inquirytype,0)),'') as inquirytypename, cq.inquirystatus, " & _
  "isnull((select cs.statusname from CustomerDB.dbo.InquiryStatuses cs where cs.statusid = 
     isnull(cq.inquirystatus,0)),'') as inquirystatusname, " & _
  "isnull((select pi.priorityname from CustomerDB.dbo.InquiryPriorities pi where pi.priorityid 
     = isnull(cq.priority,0)),'') as priorityname, " & _
  "replace(replace(replace(replace(replace(cast(isnull(cq.problem,'') as varchar(8000)),
      char(44),char(130)),char(39),char(145)),char(10),' '),char(13),' '),
    char(92),char(47)) as problem, " & _
  "replace(replace(replace(replace(replace(cast(isnull(cq.resolution,'') as varchar(8000))
    ,char(44),char(130)),char(39),char(145)),char(10),' '),char(13),' '),
    char(92),char(47)) as resolution, " & _
  "cq.resolutiondate, isnull(cq.resolved,0) as resolved, cq.promiseddate, cq.InquiryQueue, " & _
  "isnull((select usr.username from CustomerDB.dbo.users usr where usr.userid 
    = isnull(cq.userid,0)),'') as UserName, " & _
  "rtrim(ltrim(replace(upper(left(ltrim(rtrim(us.FirstName)),1))+right(ltrim(rtrim(us.FirstName)),
    len(ltrim(rtrim(us.FirstName)))-1)+' '+upper(left(ltrim(rtrim(us.LastName)),1))
    +right(ltrim(rtrim(us.LastName)),len(ltrim(rtrim(us.LastName)))-1),char(39),char(145)))) 
    as AssignName, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.fname,''),char(39),char(145)),
    char(44),char(130)),char(10),' '),char(13),' '))) as FirstName, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.lname,''),char(39),char(145)),
    char(44),char(130)),char(10),' '),char(13),' '))) as LastName, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.address1,''),char(39),char(145)),
    char(44),char(130)),char(10),' '),char(13),' '))) as Address1, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.address2,''),char(39),char(145)),
    char(44),char(130)),char(10),' '),char(13),' '))) as Address2, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.apt,''),char(39),char(145)),char(44),
    char(130)),char(10),' '),char(13),' '))) as Apt, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.city,''),char(39),char(145)),char(44),
    char(130)),char(10),' '),char(13),' '))) as City, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.state,''),char(39),char(145)),char(44),
    char(130)),char(10),' '),char(13),' '))) as State, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.countryid,''),char(39),char(145)),
    char(44),char(130)),char(10),' '),char(13),' '))) as Country, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.postalcode,''),char(39),char(145)),
    char(44),char(130)),char(10),' '),char(13),' '))) as PostalCode, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.homephone,''),char(39),char(145)),
    char(44),char(130)),char(10),' '),char(13),' '))) as HomePhone, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.officephone,''),char(39),char(145)),
    char(44),char(130)),char(10),' '),char(13),' '))) as OfficePhone, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.fax,''),char(39),char(145)),char(44),
    char(130)),char(10),' '),char(13),' '))) as Fax, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(cc.email,''),char(39),char(145)),char(44),
    char(130)),char(10),' '),char(13),' '))) as CEmail, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(us.email,''),char(39),char(145)),char(44),
    char(130)),char(10),' '),char(13),' '))) as UEmail, " & _
  "rtrim(ltrim(replace(replace(replace(replace(de.department,char(39),char(145)),char(44),
    char(130)),char(10),' '),char(13),' '))) as Department, " & _
  "rtrim(ltrim(replace(replace(replace(replace(lo.locations,char(39),char(145)),char(44),
    char(130)),char(10),' '),char(13),' '))) as Location, " & _
  "isnull(us.adminprivilege,0) as adminprivilege, " & _
  "rtrim(ltrim(replace(replace(replace(replace(isnull(us.password,''),char(39),char(145)),
    char(44),char(130)),char(10),' '),char(13),' '))) as Password, " & _
  "isnull(us.qualityassurance,0) as qualityassurance " & _
  "from CustomerDB.dbo.CustomerInquiries cq left outer join CustomerDB.dbo.users us 
     ON cq.assignedto = us.userid " & _
  "inner join CustomerDB.dbo.customers cc on cq.customerid = cc.customerid " & _
  "left outer join CustomerDB.dbo.departments de on us.department = de.id 
     left outer join itticket.dbo.locations lo on us.location = lo.id "

txtFind = ""

'Response.Write vartype(numQueueID)

If numQueueID > 0 Then
  'string concatenation, field name, value

  txtFind = txtFind & limitfind(txtFind, "cq.inquiryid", numQueueID)
End If

txtFind = txtFind & limitfind(txtFind, "cq.userid", numUserID)
txtFind = txtFind & limitfind(txtFind, "cq.assignedto", numAssignedTo)
txtFind = txtFind & limitfind(txtFind, "cq.customerid", numCustomerID)
txtFind = txtFind & limitfind(txtFind, "cq.inquiryqueue", strTicketQueue)
txtFind = txtFind & limitfind(txtFind, "cq.inquirytype", numCallTypeID)
txtFind = txtFind & limitfind(txtFind, "cq.inquirystatus", numCallStatusID)
txtFind = txtFind & limitfind(txtFind, "cq.priority", numCallPriorityID)
txtFind = txtFind & limitfind(txtFind, "cq.uemail", strUEmail)
txtFind = txtFind & limitfind(txtFind, "de.department", strDepartment)
txtFind = txtFind & limitfind(txtFind, "lo.locations", strLocation)

If txtFind <> "" Then
  sql = sql & "where " & txtFind & " "
End If
If strSortBy & "" <> "" Then
  sql = sql & "order by " & strSortBy
  If strAscDesc & "" <> "" Then
    sql = sql & " " & strAscDesc
  Else
    sql = sql & " asc"
  End If
End If

'Response.Write sql
'Response.End

rsQuery = dbc.execute(sql)
If Not rsQuery.EOF Then
  response.cookie("sql") = sql
  closeconnection()
  response.redirect("CallQuery.asp")
Else
  txtMsg = "No Call Inquiries match the criteria entered."
  bitError = True
End If
rsQuery.close()

Note: Added additional wrapping in SQL string.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!