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