If Based SQL Statement

Alright guys my code is below. The problem is that I would like this component to work reguardless of how many arguments are passed even if there are none.  I cannot say how many arguments are going to be passed and even if any will be. So the problem is where i would place my WHERE SQL Statement. I think you will get what i am saying if you look at my code. The problem with how it currently runs it that the code read "WHERE AND". I mean I guess i could put something in there that will be a definite for all rows in the table but I think there must be a better solution than that.

<cfquery datasource="#Request.DSN#" name="GetTickets">
SELECT #Arguments.TicketType#.*, Customers.*
FROM #Arguments.TicketType#
INNER JOIN Customers ON #Arguments.TicketType#.CustomerPK = Customers.CustomerPK
WHERE ***THE PROBLEM LIES HERE**
<cfif #Arguments.TicketStatus# neq "Undefined">AND #Arguments.TicketType#.TicketStatus = '#Arguments.TicketStatus#'</cfif>
<cfif #Arguments.ServiceStatus# neq "Undefined">AND #Arguments.TicketType#.ServiceStatus = '#Arguments.ServiceStatus#'</cfif>
<cfif #Arguments.TicketAssignedTo# neq "Undefined">AND #Arguments.TicketType#.TicketAssignedTo = '#Arguments.TicketAssignedTo#'</cfif>
<cfif #Arguments.CustomerName# neq "Undefined">AND #Arguments.TicketType#.TicketStatus = '#Arguments.CustomerName#'</cfif>
<cfif #Arguments.TicketID# neq "Undefined">AND #Arguments.TicketType#.TicketID = '#Arguments.TicketID#'</cfif>
<cfif #Arguments.PhoneNumber# neq "Undefined">AND Customers.PrimaryPhone = '#Arguments.PhoneNumber#' OR Customers.SecondaryPhone = '#Arguments.PhoneNumber#'</cfif>
</cfquery>
rjhunterAsked:
Who is Participating?
 
tim_csConnect With a Mentor Commented:
The easiest way, and the most commonly used way, is to use "Where 1=1" for AND statements or "Where 0=1" if you had OR statements.
0
 
73SpyderCommented:
Agree with Tim we use the 1=1 all the time.
0
 
rjhunterAuthor Commented:
lol awesome. I was hoping for a way that u didnt have to compare 2 values but this works all the same and will be more effiecient then comparing a value in the table. Thanks dude.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.