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!