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]
