>> You'd do something like this <SNIP /> Ah, the luxury of having a real RDBMS to play with!
I agree, doing some/lots of the processing (with optional input parameters) within the database is ideal but I'm having to resort to multiple SP's with <CFIF> when dealing with JET/MSAccess. at least I can get some of the benefit of compiled query convienance with SP's... thanx for the ideas. cheers barry.b -----Original Message----- From: Taco Fleur [mailto:[EMAIL PROTECTED] Sent: Monday, 23 June 2003 1:06 PM To: CFAussie Mailing List Subject: [cfaussie] RE: <CFQUERY > SQL as a string command Wow, you would not want to do all the processing outside the stored procedure like that. You'd do something like this <cfstoredproc procedure="spSearch" datasource="#request.mainDSN#"> <cfprocparam type="In" cfsqltype="CF_SQL_TINYINT" value="1" null="no"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#form.strFirstName#" maxlength="20" null="#iif(len(form.strFirstName), DE("no"), DE("yes"))#"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#form.strLastName#" maxlength="20" null="#iif(len(form.strLastName), DE("no"), DE("yes"))#"> <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#form.intEmploymentStatus#" null="#iif(len(form.intEmploymentStatus), DE("no"), DE("yes"))#"> <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#form.intBusinessUnit#" null="#iif(len(form.intBusinessUnit), DE("no"), DE("yes"))#"> <cfprocparam type="In" cfsqltype="CF_SQL_BIT" value="#form.bSoundex#" null="#iif(form.bSoundex, DE("no"), DE("yes"))#"> <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#form.intPositionTitle#" null="#iif(len(form.intPositionTitle), DE("no"), DE("yes"))#"> <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#form.intRole#" null="#iif(len(form.intRole), DE("no"), DE("yes"))#"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#form.authorCode#" maxlength="50" null="#iif(len(form.authorCode), DE("no"), DE("yes"))#"> <cfprocresult name="qResult" resultset="1"> </cfstoredproc> -- the procedure itself. CREATE PROCEDURE [dbo].[spSearch] ( @mode TINYINT = 1, @firstName VARCHAR(20) = NULL, @lastName VARCHAR(50) = NULL, @employmentStatus INT = NULL, @businessUnit INT = NULL, @soundex BIT = 0, @positionTitle INT = NULL, @role INT = NULL, @authorCode VARCHAR(50) = NULL ) AS DECLARE @sql NVARCHAR(1024) DECLARE @where NVARCHAR(5) SET @where = 'WHERE' SET @sql = ' SELECT S.pkStaffID, S.preferredName, S.firstName, S.lastName, BU.unitName, ES.employmentStatus, P.pkPositionID, PT.positionTitle, P.extensionNumber, P.returnDate, P.moveDate FROM dbo.tblSomeTable1 S INNER JOIN dbo.tblSomeTable2 P ON S.pkStaffID = P.fkStaffID INNER JOIN dbo.tblBusinessUnit BU ON P.fkBusinessUnitID = BU.pkBusinessUnitID INNER JOIN dbo.tblEmploymentStatus_LookUp ES ON P.fkEmploymentStatusID = ES.pkEmploymentStatusID INNER JOIN dbo.tblPositionTitle_LookUp PT ON P.fkPositionTitleID = PT.pkPositionTitleID WHERE (P.endDate IS NULL OR P.endDate > GETDATE()) AND (P.moveDate < GETDATE() OR P.moveDate IS NULL) AND (P.returnDate > GETDATE() OR P.returnDate IS NULL) ' IF NOT (@employmentStatus IS NULL) BEGIN SET @sql = @sql + 'AND (ES.pkEmploymentStatusID = ' + CAST(@employmentStatus AS VARCHAR(10)) + ') ' END IF NOT (@authorCode IS NULL) BEGIN SET @sql = @sql + 'AND (S.authorCode = ''' + @authorCode + ''') ' END IF NOT (@businessUnit IS NULL) BEGIN SET @sql = @sql + 'AND (BU.pkBusinessUnitID = ' + CAST(@businessUnit AS VARCHAR(10)) + ') ' END IF NOT (@positionTitle IS NULL) BEGIN SET @sql = @sql + 'AND (P.fkPositionTitleID = ' + CAST(@positionTitle AS VARCHAR(10)) + ') ' END IF NOT (@role IS NULL) BEGIN SET @sql = @sql + 'AND (SBR.fkRoleID = ' + CAST(@role AS VARCHAR(10)) + ') ' END IF @soundex = 1 BEGIN IF NOT (@firstName IS NULL) BEGIN SET @sql = @sql + 'AND (SOUNDEX(S.preferredName) = SOUNDEX(''' + @firstName + ''') OR SOUNDEX(S.firstName) = SOUNDEX(''' + @firstName + ''')) ' END IF NOT (@lastName IS NULL) BEGIN SET @sql = @sql + 'AND (SOUNDEX(S.lastName) = SOUNDEX(''' + @lastName + ''')) ' END END ELSE BEGIN IF NOT (@firstName IS NULL) BEGIN SET @sql = @sql + 'AND (S.preferredName = ''' + @firstName + ''' OR S.firstName = ''' + @firstName + ''') ' END IF NOT (@lastName IS NULL) BEGIN SET @sql = @sql + 'AND (S.lastName = ''' + @lastName + ''') ' END END SET @sql = @sql + 'ORDER BY S.lastName ' PRINT @sql EXEC sp_executesql @sql SELECT @sql as debugt GO PS. I think if you would work with an object you could use the ASP code just as well for CF, with a bit of modification offcourse.. ---------------------------------------------------------------------------- This email, together with any attachments, is intended for the named recipient(s) only and may contain privileged and confidential information. If received in error, please inform the sender as quickly as possible and delete this email and any copies from your computer system network. If not an intended recipient of this email, you must not copy, distribute or rely upon it and any form of disclosure, modification, distribution and/or publication of this email is prohibited. Unless stated otherwise, this email represents only the views of the sender and not the views of the Queensland Government. ---------------------------------------------------------------------------- --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
