I've got a great big SQL statement that I'd love to move into a stored
procedure, but I can't see any obvious way of doing so. I know a fair bit
about them, and can use them for simple queries, but as for advanced ones like
this, I'm clueless.
The main problem is that there are so many conditions where SQL needs to be
added or excluded from the statement. Any help would be hugely appreciated!
Here's my query:
<cfquery datasource="#application.dsn#" name="qryResult">
SELECT
tblAbsence.idAbsence,
tblAbsence.dteFrom as startDate,
tblAbsence.dteTo as endDate,
tblAbsence.dteFrom,
tblAbsence.dteTo,
tblAbsence.vcDetails,
tblAbsence.bApproved,
tblAbsence.keyEmployee,
tblAbsence.keyType,
tblAbsence.keyPeriod,
tblPeriod.vcName AS PeriodName,
tblType.vcName AS TypeName,
tblEmployee.vcUID,
tblType.bNeedForm,
CAST(dteFrom AS float) AS sortFrom,
CAST(dteTo AS float) AS sortTo,
'once' as eventType,
0.0 as weekdays,
'' as vcSmallFrom,
'' as vcSmallTo,
'' as vcApproved,
'' as PeriodDays
FROM tblAbsence
INNER JOIN tblType ON tblAbsence.keyType =
tblType.idType
INNER JOIN tblPeriod ON tblAbsence.keyPeriod =
tblPeriod.idPeriod
INNER JOIN tblEmployee ON tblAbsence.keyEmployee =
tblEmployee.idEmployee
WHERE 1 = 1
<cfif len(lUsers)>
<cfif left(arguments.userID,1) eq "!">
AND (tblAbsence.keyEmployee NOT IN
(<cfqueryparam cfsqltype="cf_sql_integer" value="#right(arguments.userID,
len(arguments.userID)-1)#" list="yes">))
<cfelse>
AND (tblAbsence.keyEmployee IN
(<cfqueryparam cfsqltype="cf_sql_integer" value="#lUsers#" list="yes">))
</cfif>
</cfif>
<!--- If we have a from and a to date --->
<cfif len(arguments.fromDate) and len(arguments.toDate)>
AND
(
(tblAbsence.dteFrom BETWEEN <cfqueryparam
cfsqltype="cf_sql_date" value="#arguments.fromDate#"> AND <cfqueryparam
cfsqltype="cf_sql_date" value="#arguments.toDate#">)
OR
(tblAbsence.dteTo BETWEEN <cfqueryparam
cfsqltype="cf_sql_date" value="#arguments.fromDate#"> AND <cfqueryparam
cfsqltype="cf_sql_date" value="#arguments.toDate#">)
OR
(tblAbsence.dteFrom < <cfqueryparam cfsqltype="cf_sql_date"
value="#arguments.fromDate#"> AND tblAbsence.dteTo > <cfqueryparam
cfsqltype="cf_sql_date" value="#arguments.toDate#">)
)
<!--- If we have a from date only --->
<cfelseif (len(arguments.fromDate)) and (not len(arguments.toDate))>
AND (tblAbsence.dteFrom >= <cfqueryparam
cfsqltype="cf_sql_date" value="#arguments.fromDate#">)
<!--- If we have a to date only --->
<cfelseif (len(arguments.toDate)) and (not len(arguments.fromDate))>
AND (tblAbsence.dteTo <= <cfqueryparam
cfsqltype="cf_sql_date" value="#arguments.toDate#">)
</cfif>
<cfif structKeyExists(arguments, "approved") and
len(arguments.approved)>
AND (tblAbsence.bApproved = <cfqueryparam
cfsqltype="cf_sql_bit" value="#Logic2bit(arguments.approved)#">)
</cfif>
<cfif structKeyExists(arguments, "ltype") and len(arguments.ltype)>
<cfif left(arguments.ltype,1) eq "!">
AND (tblAbsence.keyType NOT IN
(<cfqueryparam cfsqltype="cf_sql_integer" value="#right(arguments.ltype,
len(arguments.ltype)-1)#" list="yes">))
<cfelse>
AND (tblAbsence.keyType IN
(<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.ltype#"
list="yes">))
</cfif>
</cfif>
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185393
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
