Yes you can do what Mike say, just be careful where you place your spaces. In Mike's example the trailing space after the "where" would be truncated off so your where clause could end up looking like "wherebar=1" which would be invalid. It is better to put the space at the beginning of your string something like ' bar=1' to result in a string of "where bar=1"
You can also do what you want without having the string with case statements in your where clause. You would need to pass all of your form fields as parameters in the stored procedure. Your statement would then be like:
SELECT
O.opportunityId,
O.title,
O.lastUpdateUserId,
O.lastUpdateOn,
O.createDate,
U1.firstName AS creatorFirstName,
U1.lastName AS creatorLastName,
U1.emailAddress AS creatorEmailAddress,
U2.firstName AS updaterFirstName,
U2.lastName AS updaterLastName,
U2.emailAddress AS updaterEmailAddress,
P.phaseName
FROM
tbl_opportunity O
LEFT JOIN tbl_user U1 ON (O.createUserId = U1.userId)
LEFT JOIN tbl_user U2 ON (O.lastUpdateUserId = U2.userId)
LEFT JOIN tbl_phase P ON (O.phaseId = P.phaseId)
WHERE 1=0
OR 1 = CASE
WHEN FormrfpNumber <> '' AND o.rfpNumber = FormrfpNumber THEN 1
ELSE 0
END
OR 1 = CASE
WHEN FormTitle <> '' AND o.title = FormTitle THEN 1
ELSE 0
END
OR 1 = CASE
WHEN FormAwardType <> -1 AND o.awardtypeid = FormAwardType THEN 1
ELSE 0
END
ORDER BY
O.createDate DESC
______________________________________________________
<file:///E:/EUColor.gif>
Bill Grover
Supervisor MIS
Phone:
301.424.3300 x3324
EU Services, Inc.
FAX:
301.424.3696
649 North Horners Lane
E-Mail:
[EMAIL PROTECTED]
Rockville, MD 20850-1299
WWW:
<http://www.euservices.com/> http://www.euservices.com
______________________________________________________
-----Original Message-----
From: Mike Townend [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:07 AM
To: CF-Talk
Subject: RE: Query to Stored Proc
afaik you will need to build a VARCHAR of IF statements something like
DECLARE @sSQL VARCHAR(1000)
SET @sSQL = 'select * from foo where '
IF (@bar = 1)
BEGIN
SET @sSQL = @sSQL + ' bar = 1'
END
ELSE
BEGIN
SET @sSQL = @sSQL + ' 1=1'
END
exec (@sSQL)
HTH
-----Original Message-----
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 4, 2003 10:57
To: CF-Talk
Subject: Query to Stored Proc
How would you rework the logic in the WHERE clause to get this query put
into a stored proc?
Can you put conditional logic in the WHERE clause inside a stored proc?
SQL 2K Enterprise
<cfquery name="selectOpportunityResults" datasource="otis">
SELECT
O.opportunityId,
O.title,
O.lastUpdateUserId,
O.lastUpdateOn,
O.createDate,
U1.firstName AS creatorFirstName,
U1.lastName AS creatorLastName,
U1.emailAddress AS creatorEmailAddress,
U2.firstName AS updaterFirstName,
U2.lastName AS updaterLastName,
U2.emailAddress AS updaterEmailAddress,
P.phaseName
FROM
tbl_opportunity O
LEFT JOIN tbl_user U1 ON (O.createUserId = U1.userId)
LEFT JOIN tbl_user U2 ON (O.lastUpdateUserId = U2.userId)
LEFT JOIN tbl_phase P ON (O.phaseId = P.phaseId)
WHERE
1=0
<cfif form.rfpNumber NEQ "">
OR O.rfpNumber = '#form.rfpNumber#'
</cfif>
<cfif form.title NEQ "">
OR O.title = '#form.title#'
</cfif>
<cfif form.naicsCode NEQ "">
OR O.naicsCode = '#form.naicsCode#'
</cfif>
<cfif form.costCenterLocation NEQ "-1">
OR O.costCenterId = '#form.costCenterLocation#'
</cfif>
<cfif form.procurementType NEQ "-1">
OR O.procurementTypeId = '#form.procurementType#'
</cfif>
<cfif form.awardType NEQ "-1">
OR O.awardTypeId = '#form.awardType#'
</cfif>
<cfif form.classification NEQ "-1">
OR O.classificationId = '#form.classification#'
</cfif>
<cfif form.phase NEQ "-1">
OR O.phaseId = '#form.phase#'
</cfif>
<cfif form.clientAgency NEQ "-1">
OR O.agencyId = '#form.clientAgency#'
</cfif>
<cfif form.clientBureau NEQ "-1">
OR O.bureauId = '#form.clientBureau#'
</cfif>
<cfif form.clientBureauOther NEQ "-1">
OR O.bureauOtherId = '#form.clientBureauOther#'
</cfif>
ORDER BY
O.createDate DESC
</cfquery>
_____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
