I'm cleaning up some old code, can anyone say why this returns records:
<cfquery datasource="#SESSION.Datasource#" name="Cases">
execute SearchCases '#Search#', '#ReferralType#', '#CaseType#', '#NumberType#'
</cfquery>
And this doesn't:
<cfstoredproc procedure="SearchCases" datasource="#session.datasource#">
<cfprocresult name="Cases" resultset="1">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="#Search#">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="#ReferralType#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#CaseType#">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="#NumberType#">
</cfstoredproc>
Here's the sp:
CREATE PROCEDURE [SearchCases]
@Search varchar( 20),
@ReferralType varchar(5),
@CaseType int,
@NumberType varchar(4)
AS
DECLARE @sqlString varchar(2000)
DECLARE @searchVar varchar(30)
SET @searchVar = '%' + @Search + '%'
SET @sqlString = "SELECT TOP 500 Cases.CaseID,
SUBSTRING((Parties.First + ' ' + Parties.Last),1,15) AS
NCPName,SUBSTRING((Parties1.First + ' ' + Parties1.Last),1,35) AS
CPName, Cases.FileNumber,Cases.CaseNumber, Parties1.SS "
SET @sqlString = @sqlString + "FROM Cases LEFT OUTER JOIN Parties ON
Cases.CaseID = Parties.CaseID LEFT OUTER JOIN Parties Parties1 ON
Cases.CaseID = Parties1.CaseID "
SET @sqlString = @sqlString + "WHERE (Parties.PartyType = 'CP')
AND (Parties1.PartyType = 'NCP')
AND ((Parties.Last LIKE '" + @searchVar + "')
OR (Parties1.First LIKE '" + @searchVar + "')
OR (Parties1.Middle LIKE '" + @searchVar + "')
OR (Parties1.Last LIKE '" + @searchVar + "')"
IF @NumberType = 'FILE'
BEGIN
SET @sqlString = @sqlString + " OR (Cases.FileNumber LIKE '" +
@searchVar + "') "
END
IF @NumberType = 'CASE'
BEGIN
SET @sqlString = @sqlString + " OR (Cases.CaseNumber LIKE '" +
@searchVar + "') "
END
IF @NumberType = 'CSE'
BEGIN
SET @sqlString = @sqlString + " OR (Cases.CSECaseNumber LIKE '" +
@searchVar + "') "
END
IF @NumberType = 'REF'
BEGIN
SET @sqlString = @sqlString + " OR (Cases.ReferralID LIKE '" +
@searchVar + "') "
END
IF @NumberType = 'SSN'
BEGIN
SET @sqlString = @sqlString + " OR (Parties1.SS LIKE '" + @searchVar
+ "') OR (Parties.SS LIKE '" + @searchVar + "') "
END
IF @NumberType = 'ALL'
BEGIN
SET @sqlString = @sqlString + " OR (Parties1.SS LIKE '" + @searchVar
+ "') OR (Parties.SS LIKE '" + @searchVar + "') OR (Cases.CaseNumber
LIKE '" + @searchVar + "') OR (Cases.FileNumber LIKE '" +
@searchVar + "') OR (Cases.CSECaseNumber LIKE '" + @searchVar + "')
OR (Cases.ReferralID LIKE '" + @searchVar + "') "
END
SET @sqlString = @sqlString + " )"
IF @ReferralType = 'E'
BEGIN
SET @sqlString = @sqlString + "AND ReferralType = 'E'"
END
IF @ReferralType = 'R'
BEGIN
SET @sqlString = @sqlString + "AND ReferralType <> 'E'"
END
IF @CaseType = 0
BEGIN
SET @sqlString = @sqlString + "AND ArchiveBox = '' AND Closed IS NULL "
END
IF @CaseType = 1
BEGIN
SET @sqlString = @sqlString + "AND (ArchiveBox IS NOT NULL AND
ArchiveBox > ' ') "
END
IF @CaseType = 3
BEGIN
SET @sqlString = @sqlString + "AND ArchiveBox = '' AND WarrantDate IS
NOT NULL "
END
IF @CaseType = 4
BEGIN
SET @sqlString = @sqlString + "AND Closed IS NOT NULL "
END
SET @sqlString = @sqlString + 'ORDER BY Cases.FileNumber'
/* SELECT @sqlString */
EXEC( @sqlString )
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192982
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
