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!