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/
