SQL:StmtRecompile

why would this cause a recompile in my stored procedure?
any way to prevent this?
SELECT @ListIDs = ISNULL(@ListIDs + ',', '') + convert(varchar(20),[ListingID] )
FROM #List OPTION (KEEPFIXED PLAN);

That's the part that profiler is showing with SQL:StmtRecompile
Here is the full stored procedure:USE [Resweb]
GO/****** Object: StoredProcedure [dbo].[sprIndividualSearch] Script Date: 10/20/2007 18:52:37 ******/SET
ANSI_NULLS ONGOSET
QUOTED_IDENTIFIER ONGOALTER
PROCEDURE [dbo].[sprIndividualSearch](@Culture
char(5),@LastName
nvarchar(100),@FirstName
nvarchar(100),@CompanyName
nvarchar(100),@AddressLine1
nvarchar(100),@PostalCode
nvarchar(10),@City
nvarchar(100),@ProvinceIDs
varchar(250),@Designations
nvarchar(1000),@Languages
nvarchar(1000),@Specialties
nvarchar(1000),@TradingArea
nvarchar(100),@CreaIndividualID
int,@BoardIndividualID
varchar(100),@MemberShipTypeIDs
varchar (200),@PositionTypeID
varchar (200),@LicenseTypeID
varchar (200),@OrganizationTypeID
varchar (200),@StatusID
INT,@ListingIDs
nVarchar(512),@UseListingTempDB BIT, @OrgIDs
nVarchar(512),@CobrandID
AS INT,@PublicSearch
BIT,@OrderDirection
CHAR(1),@CurrentPage
int,@RecordsPerPage
int,@MaxRecords
int,@OrderBY
INT,@RecordsFound
INT OUT,@Debug
BIT = 0,@PositionTypeIDIncludingNull
as Bit = 0)AS
SET NOCOUNT ON
DECLARE
@CultureID INTDECLARE @applicationID INT
DECLARE
@CobrandTypeID intCREATE
TABLE #Results (TmpID INT IDENTITY(1,1), ID int,ListingID int, OrderByColumn nvarchar(100)) ;EXEC
dbo.sprGetCultureID @Culture, @CultureID OUT ;EXEC
dbo.sprGetApplicationID @CobrandID, @ApplicationID OUT ;--print @ApplicationIDDECLARE
@SQL AS nvarchar(4000),@SQLWhere
AS nvarchar(4000),@SQLFrom
AS nVarChar(4000),@SQLOrderBy
AS nvarchar(4000),@ReqListingIndividual
BIT,-- @ReqOrganization BIT,@ReqAddress
BIT,@ReqDesignation
BIT,@ReqLanguage
BIT,@ReqSpecialty
BIT,@ReqTradingArea
BIT,@ReqOrganization
BIT,@ReqProvinceLocale
BIT,@ReqLicense
BIT,@ReqMembershipType
BIT,@ReqOrganizationLocale
BIT,@ReqBoard
BIT,@cvs
VARCHAR(512),@pos
INT,@val2
VARCHAR(255),@val
CHAR(1),@IsFirst
BIT ;-- Default Where clauseSET
@SQLWhere = ' WHERE 1 = 1 '--last nameIF @LastName <> '' SET @SQLWhere = @SQLWhere + ' AND ' + dbo.[fnStringToSearchList]('I.LastName', @LastName)--+ ' AND I.LastName LIKE ''%'' + @LastName + ''%''' -- first name + informal first nameIF
@FirstName <> '' SET @SQLWhere = @SQLWhere + ' AND (' + dbo.[fnStringToSearchList]('I.FirstName', @FirstName) + ' OR ' + dbo.[fnStringToSearchList]('I.informalFirstName', @FirstName) + ')'-- Public SearchIF
@PublicSearch <> 0 SET @SQLWhere = @SQLWhere + ' AND I.PermitShowSearch = 1' -- crea individualidIF
@CreaIndividualID <> 0 SET @SQLWhere = @SQLWhere + ' AND I.CreaIndividualID = @CreaIndividualID'-- board individualidIF
@BoardIndividualID <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND IO2.BoardIndividualID IN (SELECT StringValue FROM dbo.CSVToStr(''' + @BoardIndividualID + '''))'
SET @ReqBoard = 1END-- membership typeIF
@MemberShipTypeIDs <> '0' AND @MemberShipTypeIDs <> '' BEGIN-- SET @SQLWhere = @SQLWhere + ' AND IO2.MemberShipTypeID IN (' + @MemberShipTypeIDs + ')'
SET @ReqBoard = 1END-- organization position (ex: mms needs specific results when searching by positionType)IF
@PositionTypeID <> '0' AND @PositionTypeID <> '' AND @PositionTypeIDIncludingNull = 0 BEGIN
SET @SQLWhere = @SQLWhere + ' AND IO.PositionTypeOrganizationID IN (' + @PositionTypeID + ')'
SET @ReqOrganization = 1END-- organization position (ex:mls needs specific results but also Nulls when searching by positionType)IF
@PositionTypeID <> '0' AND @PositionTypeID <> '' AND @PositionTypeIDIncludingNull = 1 BEGIN
SET @SQLWhere = @SQLWhere + ' AND (IO.PositionTypeOrganizationID IN (' + @PositionTypeID + ') or IO.PositionTypeOrganizationID is null)'
SET @ReqOrganization = 1END-- licence typeIF
@LicenseTypeID <> '0' AND @LicenseTypeID <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND ILIC.LicenseTypeID IN (' + @LicenseTypeID + ')'
SET @ReqLicense =1END-- organization typeIF
@OrganizationTypeID <> '0' AND @OrganizationTypeID <> '' BEGINSET @ReqOrganization = 1
SET @ReqBoard = 1
SET @SQLWhere = @SQLWhere + ' AND (O.OrganizationTypeID IN (' + @OrganizationTypeID + ') OR O2.OrganizationTypeID IN (' + @OrganizationTypeID + '))'END-- trading areaIF
@tradingArea <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND ('SET @Cvs = @tradingArea
SET @IsFirst = 1--DECLARE @CityAreaTypeID INT
DECLARE @TmpAreaID varchar(1024)
WHILE @cvs <> '' BEGIN
SET @pos = CHARINDEX(',', @cvs)
IF @pos > 0 BEGINSET @val2 = CAST(LEFT(@cvs, @pos-1) AS VARCHAR(50))
SET @cvs = RIGHT(@cvs, LEN(@cvs)-@pos)
END ELSE BEGINSET @val2 = CAST(@cvs AS VARCHAR(50))
SET @cvs = ''
END
SET @val2 = replace(@val2,'''','''''')
IF @IsFirst = 1 BEGINSET @ReqTradingArea=1
SET @IsFirst = 0
-- SET @SQLWhere = @SQLWhere + 'ITA.tradingArea LIKE ''%' + @val2 + '%'''SET @SQLWhere = @SQLWhere + ' ' + dbo.[fnStringToSearchList]('ITA.tradingArea', @val2)
END ELSE BEGIN
-- SET @SQLWhere = @SQLWhere + ' OR ITA.tradingArea LIKE ''%' + @val2 + '%'''
SET @SQLWhere = @SQLWhere + ' OR (' + dbo.[fnStringToSearchList]('ITA.tradingArea', @val2) + ')'END
END
SET @SQLWhere = @SQLWhere + ')'END-- statusIF
@StatusID <> 0 BEGIN
SET @SQLWhere = @SQLWhere + ' AND I.IndividualStatusID = @StatusID'-- SET @SQLWhere = @SQLWhere + ' AND IO2.MemberShipStatusID = @StatusID'
SET @ReqBoard = 1END-- designationsIF
@designations <> '0' AND @designations <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND IDE.DesignationID IN (' + @designations + ')'
SET @ReqDesignation=1END--languagesIF
@languages <> '0' and @languages <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND IL.LanguageID IN (' + @languages + ')'
SET @ReqLanguage=1END-- specialtiesIF
@specialties <> '0' AND @specialties <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND ISP.SpecialtyID IN (' + @specialties + ')'
SET @ReqSpecialty=1END-- organization nameIF
@CompanyName <> '' BEGINSET @ReqOrganization = 1
SET @ReqOrganization = 1SET @ReqOrganizationLocale = 1SET @SQLWhere = @SQLWhere + ' AND (' + dbo.[fnStringToSearchList]('OL.ShortName', @CompanyName) + ' OR ' + dbo.[fnStringToSearchList]('OL.LongName', @CompanyName) --Add Code for Organization Alias Search
DECLARE @TableOrganizationAlias TABLE (ID [int] IDENTITY (1, 1) NOT NULL, OrganizationAlias nvarchar(100))-- CREATE TABLE #OrganizationAlias (ID [int] IDENTITY (1, 1) NOT NULL, OrganizationAlias nvarchar(100))
INSERT INTO @TableOrganizationAlias (OrganizationAlias) SELECT OrganizationAlias FROM dbrganization_Alias WHERE OrganizationName = @CompanyName or OrganizationName = REPLACE(@CompanyName,'''','')DECLARE
@CntOrg int
DECLARE @OrganizationAlias nvarchar(100)SELECT @CntOrg = count(*) from @TableOrganizationAlias
WHILE @CntOrg <> 0
BEGINSELECT @OrganizationAlias = a.OrganizationAlias FROM @TableOrganizationAlias AS a WHERE a.ID = @CntOrg
SET @SQLWhere = @SQLWhere + ' OR OL.LongName LIKE ' + QuoteName(ltrim(rtrim('%' + @OrganizationAlias)) + '%','''') SET @CntOrg = @CntOrg - 1
END
SET @SQLWhere = @SQLWhere + ')'END
IF
@AddressLine1 <> '' AND @AddressLine1 <> '0' BEGINSET @ReqAddress = 1
SET @ReqOrganization = 1SET @ReqOrganization = 1
SET @SQLWhere = @SQLWhere + ' AND (' + dbo.[fnStringToSearchList]('A.AddressLine1', @AddressLine1) + ')'END
IF
@PostalCode <> '' AND @PostalCode <> '0' BEGINSET @ReqAddress = 1
SET @ReqOrganization = 1SET @ReqOrganization = 1
SET @SQLWhere = @SQLWhere + ' AND A.PostalCode like @PostalCode + ''%'''END-----------------------------------------------------------------------------------
-- organization city
-----------------------------------------------------------------------------------IF
@City <> '' BEGINIF CHARINDEX('''', @City)>1
BEGIN
SET @City = replace(@City,'''','')
ENDSET @IsFirst = 1
SET @ReqAddress = 1SET @ReqOrganization = 1
SET @ReqOrganization = 1/*SET @SQLWhere = @SQLWhere + ' AND (A.City LIKE ''%'' + @City + ''%''' --Add Code for City Alias Search
CREATE TABLE #CityAlias (ID [int] IDENTITY (1, 1) NOT NULL, CityAlias nvarchar(100))
INSERT INTO #CityAlias (CityAlias) SELECT CityAlias FROM CITY_ALIAS WHERE CityName = @City
DECLARE @Cnt int
DECLARE @CityAlias nvarchar(100)
SELECT @Cnt = count(*) from #CityAlias
WHILE @Cnt <> 0
BEGIN
SELECT @CityAlias = CityAlias FROM #CityAlias WHERE ID = @CNT
SET @SQLWhere = @SQLWhere + ' OR A.City LIKE ' + QuoteName(ltrim(rtrim('%' + @CityAlias)) + '%','''')
SET @Cnt = @Cnt - 1
END
SET @SQLWhere = @SQLWhere + ')'
END */
--SET @SQLWhere = @SQLWhere + ' AND (A.City LIKE ''%' + @City + '%'''
--Add Code for City Alias SearchSET @Cvs = @City
DECLARE @TableCityAlias TABLE (ID [int] IDENTITY (1, 1) NOT NULL, CityAlias nvarchar(100))-- CREATE TABLE #CityAlias (ID [int] IDENTITY (1, 1) NOT NULL, CityAlias nvarchar(100))
WHILE @cvs <> '' BEGIN
SET @pos = CHARINDEX(',', @cvs)IF @pos > 0
BEGINSET @val2 = CAST(LEFT(@cvs, @pos-1) AS VARCHAR(50))
SET @cvs = RIGHT(@cvs, LEN(@cvs)-@pos)END ELSE
BEGINSET @val2 = CAST(@cvs AS VARCHAR(50))
SET @cvs = ''
ENDIF @IsFirst = 1
BEGINSET @SQLWhere = @SQLWhere + ' AND ( A.City LIKE ''%' + @val2 + '%'''
SET @IsFirst = 0
END ELSE
BEGINSET @SQLWhere = @SQLWhere + ' OR A.City LIKE ''%' + @val2 + '%'''
END
--TRUNCATE TABLE @TableCityAliasDELETE FROM @TableCityAlias
INSERT INTO @TableCityAlias (CityAlias) SELECT CityAlias FROM dbo.CITY_ALIAS WHERE CityName = @val2
DECLARE @Cnt int
DECLARE @CityAlias nvarchar(100)SELECT @Cnt = count(*) from @TableCityAlias
WHILE @Cnt <> 0
BEGINSELECT @CityAlias = ltrim(rtrim(c.CityAlias)) FROM @TableCityAlias AS c WHERE c.ID = @CNT
SET @SQLWhere = @SQLWhere + ' OR A.City LIKE ' + QuoteName(ltrim(rtrim('%' + @CityAlias)) + '%','''') SET @Cnt = @Cnt - 1
END
END SET
@SQLWhere = @SQLWhere + ')'END-- organization provinceIF
@ProvinceIDs <> '' AND @ProvinceIDs <> '0' BEGINSET @ReqAddress = 1
SET @ReqOrganization = 1SET @ReqOrganization = 1
SET @SQLWhere = @SQLWhere + ' AND A.ProvinceID in (' + @ProvinceIDs + ') 'END------------------------------------------------
-- filter by temp table (referenced from calling sproc ie. sprListingSearch)
------------------------------------------------IF
@UseListingTempDB = 1 BEGIN
SET @ReqListingIndividual = 1END------------------------------------------------
-- search by cobrand
------------------------------------------------SELECT @CobrandTypeID
=CobrandTypeIDfrom
dbo.Cobrand_Setting with (nolock) where CobrandID=@CobrandID--IF @CobrandID <> 0 AND @CobrandID <> 1 AND @CobrandID <> 2 BEGIN
IF not @cobrandTypeID is null and @cobrandtypeid not in(5,6) BEGINSET @SQLWhere = @SQLWhere + ' AND I.IndividualID IN (SELECT ID from dbo.cobrand_id_join WITH (NOLOCK) where cobrandID = @COBRANDID AND type = ''I'' UNION ALL SELECT INDIVIDUALID AS ID from dbo.individual_organization WITH (NOLOCK) WHERE ORGANIZATIONID IN (SELECT id from dbo.cobrand_id_join
WITH (NOLOCK) where cobrandid = @cobrandid AND type = ''O'')
UNION ALL SELECT IndividualID from dbo.Individual_Organization where OrganizationID in
(Select Organizationid From dbrganization where uploadedby in (Select ID from dbo.Cobrand_ID_Join where cobrandid = @COBRANDID and type =''B'')))'
END------------------------------------------------
-- search by listing ids passed by calling sproc ie. sprIndividualDetails
------------------------------------------------SET
@Cvs = @ListingIDsSET
@IsFirst = 1WHILE
@cvs <> '' BEGIN
SET @pos = CHARINDEX(',', @cvs)
IF @pos > 0 BEGINSET @val2 = CAST(LEFT(@cvs, @pos-1) AS VARCHAR(10))
SET @cvs = RIGHT(@cvs, LEN(@cvs)-@pos)
END ELSE BEGINSET @val2 = CAST(@cvs AS VARCHAR(10))
SET @cvs = ''
END
IF @IsFirst = 1 BEGINSET @ReqListingIndividual = 1
SET @IsFirst = 0
SET @SQLWhere = @SQLWhere + ' AND LI.ListingID IN (' + quotename(rtrim(@val2),'''')
END ELSE BEGIN
SET @SQLWhere = @SQLWhere + ',' + quotename(rtrim(@val2),'''')
END END
IF
@IsFirst = 0 BEGIN
SET @SQLWhere = @SQLWhere + ')'END-- organizationif
@OrgIDs <> '' and @OrgIDs <>'0'BEGINSET @ReqOrganization = 1
SET @ReqBoard = 1
SET @SQLWhere = @SQLWhere + ' AND (IO2.OrganizationID in (' + @OrgIDs + ') OR IO.OrganizationID in (' + @OrgIDs + ')) 'END------------------------------------------------
-- ORDER BY
------------------------------------------------
--IF @OrderBY = 1 SET @SQLOrderBy = 'I.IndividualID'IF
@OrderBY = 1 SET @SQLOrderBy = 'I.CreaIndividualID' ELSE
IF @OrderBY = 2 SET @SQLOrderBy = 'I.FirstName' ELSE
IF @OrderBY = 3 SET @SQLOrderBy = 'I.LastName' ELSE
IF @OrderBY = 4 SET @SQLOrderBy = 'OL2.Code' ELSE
IF @OrderBY = 5 SET @SQLOrderBy = 'Io.BoardIndividualID' ELSE
IF @OrderBY = 6 SET @SQLOrderBy = 'MT.longName' ELSE
IF @OrderBY = 7 SET @SQLOrderBy = 'ol.longName' ELSE
IF @OrderBY = 8 SET @SQLOrderBy = 'A.City' ELSE
IF @OrderBY = 9 SET @SQLOrderBy = 'P.LongName' ELSE
IF @OrderBY = 10 SET @SQLOrderBy = 'LI.SortBy' ELSE
SET @SQLOrderBy = 'I.LastName' IF
@OrderBy in (4,5,6,7,8,9) SET @ReqOrganization = 1IF
@OrderBy in (4,6) SET @ReqBoard = 1IF
@OrderBy in (6) SET @ReqMembershipType = 1IF
@OrderBy in (8,9) SET @ReqAddress = 1 IF
@OrderBy in (9) SET @ReqProvinceLocale = 1IF
@OrderBy in (10) SET @UseListingTempDB = 1------------------------------------------------
-- FROM
------------------------------------------------SET
@SQLFrom = ' FROM dbo.Individual I with (nolock)'IF
@ReqListingIndividual = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Listing_Individual LI with (nolock) on I.IndividualID = LI.IndividualID'IF @ReqOrganization = 1 SET @SQLFrom = @SQLFrom + ' LEFT JOIN (
dbo.INDIVIDUAL_ORGANIZATION IO WITH (NOLOCK)
JOIN dbRGANIZATION O WITH (NOLOCK) ON IO.ORGANIZATIONID = O.ORGANIZATIONID and IO.StatusID = 3
LEFT JOIN dbRGANIZATION_LOCALE OL WITH (NOLOCK) ON O.ORGANIZATIONID = OL.ORGANIZATIONID AND OL.CultureID = @CultureID
LEFT OUTER JOIN dbRGANIZATION_ADDRESS OAA with (nolock) ON OAA.ORGANIZATIONID = O.ORGANIZATIONID and OAA.AddressTypeID = 3
) ON I.INDIVIDUALID = IO.INDIVIDUALID and IO.RelationShipTypeID = 1'IF @ReqBoard = 1
BEGINSET @SQLFrom = @SQLFrom + ' LEFT JOIN (
dbo.INDIVIDUAL_ORGANIZATION IO2 WITH (NOLOCK)
JOIN dbRGANIZATION O2 WITH (NOLOCK) ON IO2.ORGANIZATIONID = O2.ORGANIZATIONID
LEFT JOIN dbRGANIZATION_LOCALE OL2 WITH (NOLOCK) ON O2.ORGANIZATIONID = OL2.ORGANIZATIONID AND OL2.CultureID =@CultureID
) ON I.INDIVIDUALID = IO2.INDIVIDUALID AND IO2.RelationShipTypeID = 2 AND IO2.PrimaryMembership = 1'
END
IF @MemberShipTypeIDs <> '0' AND @MemberShipTypeIDs <> '' BEGIN
SET @SQLFrom = @SQLFrom + 'JOIN dbo.INDIVIDUAL_ORGANIZATION IO3 WITH (NOLOCK) on I.IndividualID = IO3.IndividualID and IO3.MemberShipTypeID IN (' + @MemberShipTypeIDs + ')'
ENDIF
@ReqAddress = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Address A with (nolock) ON OAA.AddressID = A.AddressID AND A.[Primary] = 1'IF
@ReqDesignation = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Individual_Designation IDE with (nolock) ON IDE.IndividualID = I.IndividualID'IF
@ReqLanguage = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Individual_Language IL with (nolock) ON IL.IndividualID = I.IndividualID'IF
@ReqSpecialty= 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Individual_Specialty ISP with (nolock) ON ISP.IndividualID = I.IndividualID'IF
@ReqTradingArea= 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Individual_trading_area ITA with (nolock) ON ITA.IndividualID = I.IndividualID and ITA.CultureID = ' + cast(@CultureID as varchar)IF
@UseListingTempDB = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN #List ON LI.ListingID = #List.ListingID'IF
@ReqProvinceLocale = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Province P with (nolock) ON A.ProvinceID = P.ProvinceID AND P.CultureID = @CultureID'IF
@ReqLicense =1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.INDIVIDUAL_LICENSE ILic with (nolock) ON I.IndividualID=ILic.IndividualID'IF
@ReqMembershipType = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.MEMBERSHIP_TYPE MT on MT.MemberShipTypeID = IO2.MemberShipTypeID and MT.CultureID = @CultureID'IF @MaxRecords <> 0
BEGIN
SET @SQL = 'SELECT DISTINCT TOP ' + cast((@MaxRecords+1) as varchar) + ' I.IndividualID as ID,'END
ELSE
BEGIN
SET @SQL = 'SELECT DISTINCT I.IndividualID as ID,'END
IF
@ReqListingIndividual = 1
SET @SQL = @SQL + ' LI.ListingID 'ELSE
SET @SQL = @SQL +' '''' 'SET
@SQL = @SQL + ', ' + @SQLOrderBY IF
@OrderDirection = 'D' SET @SQLOrderBy = @SQLOrderBy + ' DESC'ELSE
SET @SQLOrderBy = @SQLOrderBy + ' ASC'SET @SQL = @SQL + @SQLFrom + @SQlWhere + ' ORDER BY ' + @SQLOrderBY IF
@Debug = 1 BEGIN
PRINT 'Ind Search'
PRINT 'SQL:'
PRINT @SQLEND-- Generate search results (Table 0 in the dataset)SET
@SQL = 'INSERT INTO #Results (ID, ListingID, OrderByColumn) ' + @SQLEXEC sp_executesql @SQL, N'@LastName nvarchar(100), @FirstName nvarchar(100), @CreaIndividualID int, @BoardIndividualID nvarchar(100)
, @MemberShipTypeIDs nvarchar(200), @PositionTypeID nvarchar(200), @LicenseTypeID nvarchar(200), @OrganizationTypeID nvarchar(200)
, @StatusID int, @designations nvarchar(1000), @languages nvarchar(1000), @specialties nvarchar(1000), @CompanyName nvarchar(100)
, @AddressLine1 nvarchar(100), @PostalCode nvarchar(10), @City nvarchar(100), @ProvinceIDs nvarchar(250), @COBRANDID int
, @OrgIDs nVarchar(512), @CultureID int',
@LastName,@FirstName,@CreaIndividualID,@BoardIndividualID,
@MemberShipTypeIDs,@PositionTypeID,@LicenseTypeID,@OrganizationTypeID,
@StatusID,@designations,@languages,@specialties,@CompanyName,
@AddressLine1,@PostalCode,@City,@ProvinceIDs,@COBRANDID,
@OrgIDs,@CultureID ;SELECT
* FROM #Results ;SET
@RecordsFound = @@RowCount ;-- Generate first page of results (Table 1 in the dataset)IF
@UseListingTempDB = 0 BEGIN
-- Stand alone individual search (MMS or MLS)
DECLARE @FirstRec int
DECLARE @LastRec intIF @RecordsPerPage <> 0
BEGINSET @FirstRec = (@CurrentPage - 1) * @RecordsPerPage + 1
SET @LastRec = (@CurrentPage * @RecordsPerPage)
END
ELSE
BEGINSET @FirstRec = 0
Select @LastRec = MAX(TmpID) FROM #Results
END
DECLARE @CurrentResults nVARCHAR(2000)
-- SELECT @CurrentResults = ISNULL(@CurrentResults + ',', '') + convert(varchar(200),[ID] )
-- FROM (select distinct id from #Results WHERE TmpID >= @FirstRec AND TmpID <= @LastRec) t
-- print @CurrentResultsIF @OrderDirection = 'D'
BEGIN
SELECT @CurrentResults = ISNULL(@CurrentResults + ',', '') + convert(varchar(200),[ID] )FROM #Results
WHERE TmpID >= @FirstRec AND TmpID <= @LastRec
ORDER BY OrderByColumn DESC ;
ENDELSE
BEGIN
SELECT @CurrentResults = ISNULL(@CurrentResults + ',', '') + convert(varchar(200),[ID] )FROM #Results
WHERE TmpID >= @FirstRec AND TmpID <= @LastRec
ORDER BY OrderByColumn ASC ;END
END
ELSE
-- MLS Listing Search
BEGIN
SELECT @CurrentResults = ISNULL(@CurrentResults + ',', '') + convert(varchar(200),[ID] )
FROM (select distinct id from #Results) t ;END
DECLARE
@ListIDs VARCHAR(8000) ;IF
@UseListingTempDB = 1BEGIN
-- Build a list of Listing IDs from the #List table
SELECT @ListIDs = ISNULL(@ListIDs + ',', '') + convert(varchar(20),[ListingID] )
FROM #List OPTION (KEEPFIXED PLAN);END
ELSE
SET @ListIDS = NULL ;EXEC dbo.sprIndividualSearchDetails @CurrentResults, @CultureID, @UseListingTempDB, @PublicSearch, @cobrandID, @ListIDs ;

That's the part that profiler is showing with SQL:StmtRecompile
Here is the full stored procedure:USE [Resweb]
GO/****** Object: StoredProcedure [dbo].[sprIndividualSearch] Script Date: 10/20/2007 18:52:37 ******/SET
ANSI_NULLS ONGOSET
QUOTED_IDENTIFIER ONGOALTER
PROCEDURE [dbo].[sprIndividualSearch](@Culture
char(5),@LastName
nvarchar(100),@FirstName
nvarchar(100),@CompanyName
nvarchar(100),@AddressLine1
nvarchar(100),@PostalCode
nvarchar(10),@City
nvarchar(100),@ProvinceIDs
varchar(250),@Designations
nvarchar(1000),@Languages
nvarchar(1000),@Specialties
nvarchar(1000),@TradingArea
nvarchar(100),@CreaIndividualID
int,@BoardIndividualID
varchar(100),@MemberShipTypeIDs
varchar (200),@PositionTypeID
varchar (200),@LicenseTypeID
varchar (200),@OrganizationTypeID
varchar (200),@StatusID
INT,@ListingIDs
nVarchar(512),@UseListingTempDB BIT, @OrgIDs
nVarchar(512),@CobrandID
AS INT,@PublicSearch
BIT,@OrderDirection
CHAR(1),@CurrentPage
int,@RecordsPerPage
int,@MaxRecords
int,@OrderBY
INT,@RecordsFound
INT OUT,@Debug
BIT = 0,@PositionTypeIDIncludingNull
as Bit = 0)AS
SET NOCOUNT ON
DECLARE
@CultureID INTDECLARE @applicationID INT DECLARE @CobrandTypeID int
CREATE
TABLE #Results (TmpID INT IDENTITY(1,1), ID int,ListingID int, OrderByColumn nvarchar(100)) ;EXEC
dbo.sprGetCultureID @Culture, @CultureID OUT ;EXEC
dbo.sprGetApplicationID @CobrandID, @ApplicationID OUT ;--print @ApplicationIDDECLARE
@SQL AS nvarchar(4000),@SQLWhere
AS nvarchar(4000),@SQLFrom
AS nVarChar(4000),@SQLOrderBy
AS nvarchar(4000),@ReqListingIndividual
BIT,-- @ReqOrganization BIT,@ReqAddress
BIT,@ReqDesignation
BIT,@ReqLanguage
BIT,@ReqSpecialty
BIT,@ReqTradingArea
BIT,@ReqOrganization
BIT,@ReqProvinceLocale
BIT,@ReqLicense
BIT,@ReqMembershipType
BIT,@ReqOrganizationLocale
BIT,@ReqBoard
BIT,@cvs
VARCHAR(512),@pos
INT,@val2
VARCHAR(255),@val
CHAR(1),@IsFirst
BIT ;-- Default Where clauseSET
@SQLWhere = ' WHERE 1 = 1 '--last nameIF @LastName <> '' SET @SQLWhere = @SQLWhere + ' AND ' + dbo.[fnStringToSearchList]('I.LastName', @LastName)--+ ' AND I.LastName LIKE ''%'' + @LastName + ''%''' -- first name + informal first nameIF
@FirstName <> '' SET @SQLWhere = @SQLWhere + ' AND (' + dbo.[fnStringToSearchList]('I.FirstName', @FirstName) + ' OR ' + dbo.[fnStringToSearchList]('I.informalFirstName', @FirstName) + ')'-- Public SearchIF
@PublicSearch <> 0 SET @SQLWhere = @SQLWhere + ' AND I.PermitShowSearch = 1' -- crea individualidIF
@CreaIndividualID <> 0 SET @SQLWhere = @SQLWhere + ' AND I.CreaIndividualID = @CreaIndividualID'-- board individualidIF
@BoardIndividualID <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND IO2.BoardIndividualID IN (SELECT StringValue FROM dbo.CSVToStr(''' + @BoardIndividualID + '''))'
SET @ReqBoard = 1END-- membership typeIF
@MemberShipTypeIDs <> '0' AND @MemberShipTypeIDs <> '' BEGIN-- SET @SQLWhere = @SQLWhere + ' AND IO2.MemberShipTypeID IN (' + @MemberShipTypeIDs + ')'
SET @ReqBoard = 1END-- organization position (ex: mms needs specific results when searching by positionType)IF
@PositionTypeID <> '0' AND @PositionTypeID <> '' AND @PositionTypeIDIncludingNull = 0 BEGIN
SET @SQLWhere = @SQLWhere + ' AND IO.PositionTypeOrganizationID IN (' + @PositionTypeID + ')'
SET @ReqOrganization = 1END-- organization position (ex:mls needs specific results but also Nulls when searching by positionType)IF
@PositionTypeID <> '0' AND @PositionTypeID <> '' AND @PositionTypeIDIncludingNull = 1 BEGIN
SET @SQLWhere = @SQLWhere + ' AND (IO.PositionTypeOrganizationID IN (' + @PositionTypeID + ') or IO.PositionTypeOrganizationID is null)'
SET @ReqOrganization = 1END-- licence typeIF
@LicenseTypeID <> '0' AND @LicenseTypeID <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND ILIC.LicenseTypeID IN (' + @LicenseTypeID + ')'
SET @ReqLicense =1END-- organization typeIF
@OrganizationTypeID <> '0' AND @OrganizationTypeID <> '' BEGINSET @ReqOrganization = 1 SET @ReqBoard = 1
SET @SQLWhere = @SQLWhere + ' AND (O.OrganizationTypeID IN (' + @OrganizationTypeID + ') OR O2.OrganizationTypeID IN (' + @OrganizationTypeID + '))'END-- trading areaIF
@tradingArea <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND ('SET @Cvs = @tradingArea
SET @IsFirst = 1--DECLARE @CityAreaTypeID INT
DECLARE @TmpAreaID varchar(1024)
WHILE @cvs <> '' BEGIN
SET @pos = CHARINDEX(',', @cvs)
IF @pos > 0 BEGINSET @val2 = CAST(LEFT(@cvs, @pos-1) AS VARCHAR(50))
SET @cvs = RIGHT(@cvs, LEN(@cvs)-@pos)
END ELSE BEGINSET @val2 = CAST(@cvs AS VARCHAR(50))
SET @cvs = ''
END
SET @val2 = replace(@val2,'''','''''')
IF @IsFirst = 1 BEGINSET @ReqTradingArea=1 SET @IsFirst = 0
-- SET @SQLWhere = @SQLWhere + 'ITA.tradingArea LIKE ''%' + @val2 + '%'''SET @SQLWhere = @SQLWhere + ' ' + dbo.[fnStringToSearchList]('ITA.tradingArea', @val2)
END ELSE BEGIN
-- SET @SQLWhere = @SQLWhere + ' OR ITA.tradingArea LIKE ''%' + @val2 + '%'''
SET @SQLWhere = @SQLWhere + ' OR (' + dbo.[fnStringToSearchList]('ITA.tradingArea', @val2) + ')'END
END
SET @SQLWhere = @SQLWhere + ')'END-- statusIF
@StatusID <> 0 BEGIN
SET @SQLWhere = @SQLWhere + ' AND I.IndividualStatusID = @StatusID'-- SET @SQLWhere = @SQLWhere + ' AND IO2.MemberShipStatusID = @StatusID'
SET @ReqBoard = 1END-- designationsIF
@designations <> '0' AND @designations <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND IDE.DesignationID IN (' + @designations + ')'
SET @ReqDesignation=1END--languagesIF
@languages <> '0' and @languages <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND IL.LanguageID IN (' + @languages + ')'
SET @ReqLanguage=1END-- specialtiesIF
@specialties <> '0' AND @specialties <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND ISP.SpecialtyID IN (' + @specialties + ')'
SET @ReqSpecialty=1END-- organization nameIF
@CompanyName <> '' BEGINSET @ReqOrganization = 1
SET @ReqOrganization = 1SET @ReqOrganizationLocale = 1SET @SQLWhere = @SQLWhere + ' AND (' + dbo.[fnStringToSearchList]('OL.ShortName', @CompanyName) + ' OR ' + dbo.[fnStringToSearchList]('OL.LongName', @CompanyName) --Add Code for Organization Alias Search
DECLARE @TableOrganizationAlias TABLE (ID [int] IDENTITY (1, 1) NOT NULL, OrganizationAlias nvarchar(100))-- CREATE TABLE #OrganizationAlias (ID [int] IDENTITY (1, 1) NOT NULL, OrganizationAlias nvarchar(100))
INSERT INTO @TableOrganizationAlias (OrganizationAlias) SELECT OrganizationAlias FROM dbrganization_Alias WHERE OrganizationName = @CompanyName or OrganizationName = REPLACE(@CompanyName,'''','')DECLARE
@CntOrg int
DECLARE @OrganizationAlias nvarchar(100)SELECT @CntOrg = count(*) from @TableOrganizationAlias WHILE @CntOrg <> 0
BEGINSELECT @OrganizationAlias = a.OrganizationAlias FROM @TableOrganizationAlias AS a WHERE a.ID = @CntOrg
SET @SQLWhere = @SQLWhere + ' OR OL.LongName LIKE ' + QuoteName(ltrim(rtrim('%' + @OrganizationAlias)) + '%','''') SET @CntOrg = @CntOrg - 1
END
SET @SQLWhere = @SQLWhere + ')'END
IF
@AddressLine1 <> '' AND @AddressLine1 <> '0' BEGINSET @ReqAddress = 1
SET @ReqOrganization = 1SET @ReqOrganization = 1
SET @SQLWhere = @SQLWhere + ' AND (' + dbo.[fnStringToSearchList]('A.AddressLine1', @AddressLine1) + ')'END
IF
@PostalCode <> '' AND @PostalCode <> '0' BEGINSET @ReqAddress = 1
SET @ReqOrganization = 1SET @ReqOrganization = 1
SET @SQLWhere = @SQLWhere + ' AND A.PostalCode like @PostalCode + ''%'''END-----------------------------------------------------------------------------------
-- organization city
-----------------------------------------------------------------------------------IF
@City <> '' BEGINIF CHARINDEX('''', @City)>1 BEGIN
SET @City = replace(@City,'''','')
ENDSET @IsFirst = 1
SET @ReqAddress = 1SET @ReqOrganization = 1
SET @ReqOrganization = 1/*SET @SQLWhere = @SQLWhere + ' AND (A.City LIKE ''%'' + @City + ''%''' --Add Code for City Alias Search
CREATE TABLE #CityAlias (ID [int] IDENTITY (1, 1) NOT NULL, CityAlias nvarchar(100))
INSERT INTO #CityAlias (CityAlias) SELECT CityAlias FROM CITY_ALIAS WHERE CityName = @City
DECLARE @Cnt int
DECLARE @CityAlias nvarchar(100)
SELECT @Cnt = count(*) from #CityAlias
WHILE @Cnt <> 0
BEGIN
SELECT @CityAlias = CityAlias FROM #CityAlias WHERE ID = @CNT
SET @SQLWhere = @SQLWhere + ' OR A.City LIKE ' + QuoteName(ltrim(rtrim('%' + @CityAlias)) + '%','''')
SET @Cnt = @Cnt - 1
END
SET @SQLWhere = @SQLWhere + ')'
END */
--SET @SQLWhere = @SQLWhere + ' AND (A.City LIKE ''%' + @City + '%'''
--Add Code for City Alias SearchSET @Cvs = @City
DECLARE @TableCityAlias TABLE (ID [int] IDENTITY (1, 1) NOT NULL, CityAlias nvarchar(100))-- CREATE TABLE #CityAlias (ID [int] IDENTITY (1, 1) NOT NULL, CityAlias nvarchar(100))
WHILE @cvs <> '' BEGIN
SET @pos = CHARINDEX(',', @cvs)IF @pos > 0
BEGINSET @val2 = CAST(LEFT(@cvs, @pos-1) AS VARCHAR(50))
SET @cvs = RIGHT(@cvs, LEN(@cvs)-@pos)END ELSE
BEGINSET @val2 = CAST(@cvs AS VARCHAR(50))
SET @cvs = ''
ENDIF @IsFirst = 1
BEGINSET @SQLWhere = @SQLWhere + ' AND ( A.City LIKE ''%' + @val2 + '%''' SET @IsFirst = 0
END ELSE
BEGINSET @SQLWhere = @SQLWhere + ' OR A.City LIKE ''%' + @val2 + '%'''
END
--TRUNCATE TABLE @TableCityAliasDELETE FROM @TableCityAlias INSERT INTO @TableCityAlias (CityAlias) SELECT CityAlias FROM dbo.CITY_ALIAS WHERE CityName = @val2
DECLARE @Cnt int
DECLARE @CityAlias nvarchar(100)SELECT @Cnt = count(*) from @TableCityAlias WHILE @Cnt <> 0
BEGINSELECT @CityAlias = ltrim(rtrim(c.CityAlias)) FROM @TableCityAlias AS c WHERE c.ID = @CNT
SET @SQLWhere = @SQLWhere + ' OR A.City LIKE ' + QuoteName(ltrim(rtrim('%' + @CityAlias)) + '%','''') SET @Cnt = @Cnt - 1
END
END SET
@SQLWhere = @SQLWhere + ')'END-- organization provinceIF
@ProvinceIDs <> '' AND @ProvinceIDs <> '0' BEGINSET @ReqAddress = 1
SET @ReqOrganization = 1SET @ReqOrganization = 1
SET @SQLWhere = @SQLWhere + ' AND A.ProvinceID in (' + @ProvinceIDs + ') 'END------------------------------------------------
-- filter by temp table (referenced from calling sproc ie. sprListingSearch)
------------------------------------------------IF
@UseListingTempDB = 1 BEGIN
SET @ReqListingIndividual = 1END------------------------------------------------
-- search by cobrand
------------------------------------------------SELECT @CobrandTypeID
=CobrandTypeIDfrom
dbo.Cobrand_Setting with (nolock) where CobrandID=@CobrandID--IF @CobrandID <> 0 AND @CobrandID <> 1 AND @CobrandID <> 2 BEGIN
IF not @cobrandTypeID is null and @cobrandtypeid not in(5,6) BEGINSET @SQLWhere = @SQLWhere + ' AND I.IndividualID IN (SELECT ID from dbo.cobrand_id_join WITH (NOLOCK) where cobrandID = @COBRANDID AND type = ''I'' UNION ALL SELECT INDIVIDUALID AS ID from dbo.individual_organization WITH (NOLOCK) WHERE ORGANIZATIONID IN (SELECT id from dbo.cobrand_id_join
WITH (NOLOCK) where cobrandid = @cobrandid AND type = ''O'')
UNION ALL SELECT IndividualID from dbo.Individual_Organization where OrganizationID in
(Select Organizationid From dbrganization where uploadedby in (Select ID from dbo.Cobrand_ID_Join where cobrandid = @COBRANDID and type =''B'')))'
END------------------------------------------------
-- search by listing ids passed by calling sproc ie. sprIndividualDetails
------------------------------------------------SET
@Cvs = @ListingIDsSET
@IsFirst = 1WHILE
@cvs <> '' BEGIN
SET @pos = CHARINDEX(',', @cvs)
IF @pos > 0 BEGINSET @val2 = CAST(LEFT(@cvs, @pos-1) AS VARCHAR(10))
SET @cvs = RIGHT(@cvs, LEN(@cvs)-@pos)
END ELSE BEGINSET @val2 = CAST(@cvs AS VARCHAR(10))
SET @cvs = ''
END
IF @IsFirst = 1 BEGINSET @ReqListingIndividual = 1 SET @IsFirst = 0
SET @SQLWhere = @SQLWhere + ' AND LI.ListingID IN (' + quotename(rtrim(@val2),'''')
END ELSE BEGIN
SET @SQLWhere = @SQLWhere + ',' + quotename(rtrim(@val2),'''')
END END
IF
@IsFirst = 0 BEGIN
SET @SQLWhere = @SQLWhere + ')'END-- organizationif
@OrgIDs <> '' and @OrgIDs <>'0'BEGINSET @ReqOrganization = 1 SET @ReqBoard = 1
SET @SQLWhere = @SQLWhere + ' AND (IO2.OrganizationID in (' + @OrgIDs + ') OR IO.OrganizationID in (' + @OrgIDs + ')) 'END------------------------------------------------
-- ORDER BY
------------------------------------------------
--IF @OrderBY = 1 SET @SQLOrderBy = 'I.IndividualID'IF
@OrderBY = 1 SET @SQLOrderBy = 'I.CreaIndividualID' ELSE
IF @OrderBY = 2 SET @SQLOrderBy = 'I.FirstName' ELSE
IF @OrderBY = 3 SET @SQLOrderBy = 'I.LastName' ELSE
IF @OrderBY = 4 SET @SQLOrderBy = 'OL2.Code' ELSE
IF @OrderBY = 5 SET @SQLOrderBy = 'Io.BoardIndividualID' ELSE
IF @OrderBY = 6 SET @SQLOrderBy = 'MT.longName' ELSE
IF @OrderBY = 7 SET @SQLOrderBy = 'ol.longName' ELSE
IF @OrderBY = 8 SET @SQLOrderBy = 'A.City' ELSE
IF @OrderBY = 9 SET @SQLOrderBy = 'P.LongName' ELSE
IF @OrderBY = 10 SET @SQLOrderBy = 'LI.SortBy' ELSE
SET @SQLOrderBy = 'I.LastName' IF
@OrderBy in (4,5,6,7,8,9) SET @ReqOrganization = 1IF
@OrderBy in (4,6) SET @ReqBoard = 1IF
@OrderBy in (6) SET @ReqMembershipType = 1IF
@OrderBy in (8,9) SET @ReqAddress = 1 IF
@OrderBy in (9) SET @ReqProvinceLocale = 1IF
@OrderBy in (10) SET @UseListingTempDB = 1------------------------------------------------
-- FROM
------------------------------------------------SET
@SQLFrom = ' FROM dbo.Individual I with (nolock)'IF
@ReqListingIndividual = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Listing_Individual LI with (nolock) on I.IndividualID = LI.IndividualID'IF @ReqOrganization = 1 SET @SQLFrom = @SQLFrom + ' LEFT JOIN (
dbo.INDIVIDUAL_ORGANIZATION IO WITH (NOLOCK)
JOIN dbRGANIZATION O WITH (NOLOCK) ON IO.ORGANIZATIONID = O.ORGANIZATIONID and IO.StatusID = 3
LEFT JOIN dbRGANIZATION_LOCALE OL WITH (NOLOCK) ON O.ORGANIZATIONID = OL.ORGANIZATIONID AND OL.CultureID = @CultureID
LEFT OUTER JOIN dbRGANIZATION_ADDRESS OAA with (nolock) ON OAA.ORGANIZATIONID = O.ORGANIZATIONID and OAA.AddressTypeID = 3
) ON I.INDIVIDUALID = IO.INDIVIDUALID and IO.RelationShipTypeID = 1'IF @ReqBoard = 1
BEGINSET @SQLFrom = @SQLFrom + ' LEFT JOIN (
dbo.INDIVIDUAL_ORGANIZATION IO2 WITH (NOLOCK)
JOIN dbRGANIZATION O2 WITH (NOLOCK) ON IO2.ORGANIZATIONID = O2.ORGANIZATIONID
LEFT JOIN dbRGANIZATION_LOCALE OL2 WITH (NOLOCK) ON O2.ORGANIZATIONID = OL2.ORGANIZATIONID AND OL2.CultureID =@CultureID
) ON I.INDIVIDUALID = IO2.INDIVIDUALID AND IO2.RelationShipTypeID = 2 AND IO2.PrimaryMembership = 1'
END
IF @MemberShipTypeIDs <> '0' AND @MemberShipTypeIDs <> '' BEGIN
SET @SQLFrom = @SQLFrom + 'JOIN dbo.INDIVIDUAL_ORGANIZATION IO3 WITH (NOLOCK) on I.IndividualID = IO3.IndividualID and IO3.MemberShipTypeID IN (' + @MemberShipTypeIDs + ')'
ENDIF
@ReqAddress = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Address A with (nolock) ON OAA.AddressID = A.AddressID AND A.[Primary] = 1'IF
@ReqDesignation = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Individual_Designation IDE with (nolock) ON IDE.IndividualID = I.IndividualID'IF
@ReqLanguage = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Individual_Language IL with (nolock) ON IL.IndividualID = I.IndividualID'IF
@ReqSpecialty= 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Individual_Specialty ISP with (nolock) ON ISP.IndividualID = I.IndividualID'IF
@ReqTradingArea= 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Individual_trading_area ITA with (nolock) ON ITA.IndividualID = I.IndividualID and ITA.CultureID = ' + cast(@CultureID as varchar)IF
@UseListingTempDB = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN #List ON LI.ListingID = #List.ListingID'IF
@ReqProvinceLocale = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.Province P with (nolock) ON A.ProvinceID = P.ProvinceID AND P.CultureID = @CultureID'IF
@ReqLicense =1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.INDIVIDUAL_LICENSE ILic with (nolock) ON I.IndividualID=ILic.IndividualID'IF
@ReqMembershipType = 1 SET @SQLFrom = @SQLFrom + ' LEFT OUTER JOIN dbo.MEMBERSHIP_TYPE MT on MT.MemberShipTypeID = IO2.MemberShipTypeID and MT.CultureID = @CultureID'IF @MaxRecords <> 0 BEGIN
SET @SQL = 'SELECT DISTINCT TOP ' + cast((@MaxRecords+1) as varchar) + ' I.IndividualID as ID,'END
ELSE
BEGIN
SET @SQL = 'SELECT DISTINCT I.IndividualID as ID,'END IF @ReqListingIndividual = 1
SET @SQL = @SQL + ' LI.ListingID 'ELSE
SET @SQL = @SQL +' '''' 'SET
@SQL = @SQL + ', ' + @SQLOrderBY IF
@OrderDirection = 'D' SET @SQLOrderBy = @SQLOrderBy + ' DESC'ELSE
SET @SQLOrderBy = @SQLOrderBy + ' ASC'SET @SQL = @SQL + @SQLFrom + @SQlWhere + ' ORDER BY ' + @SQLOrderBY IF
@Debug = 1 BEGIN
PRINT 'Ind Search'
PRINT 'SQL:'
PRINT @SQLEND-- Generate search results (Table 0 in the dataset)SET
@SQL = 'INSERT INTO #Results (ID, ListingID, OrderByColumn) ' + @SQLEXEC sp_executesql @SQL, N'@LastName nvarchar(100), @FirstName nvarchar(100), @CreaIndividualID int, @BoardIndividualID nvarchar(100)
, @MemberShipTypeIDs nvarchar(200), @PositionTypeID nvarchar(200), @LicenseTypeID nvarchar(200), @OrganizationTypeID nvarchar(200)
, @StatusID int, @designations nvarchar(1000), @languages nvarchar(1000), @specialties nvarchar(1000), @CompanyName nvarchar(100)
, @AddressLine1 nvarchar(100), @PostalCode nvarchar(10), @City nvarchar(100), @ProvinceIDs nvarchar(250), @COBRANDID int
, @OrgIDs nVarchar(512), @CultureID int',
@LastName,@FirstName,@CreaIndividualID,@BoardIndividualID,
@MemberShipTypeIDs,@PositionTypeID,@LicenseTypeID,@OrganizationTypeID,
@StatusID,@designations,@languages,@specialties,@CompanyName,
@AddressLine1,@PostalCode,@City,@ProvinceIDs,@COBRANDID,
@OrgIDs,@CultureID ;SELECT
* FROM #Results ;SET
@RecordsFound = @@RowCount ;-- Generate first page of results (Table 1 in the dataset)IF
@UseListingTempDB = 0 BEGIN
-- Stand alone individual search (MMS or MLS)
DECLARE @FirstRec int
DECLARE @LastRec intIF @RecordsPerPage <> 0
BEGINSET @FirstRec = (@CurrentPage - 1) * @RecordsPerPage + 1
SET @LastRec = (@CurrentPage * @RecordsPerPage)
END
ELSE
BEGINSET @FirstRec = 0 Select @LastRec = MAX(TmpID) FROM #Results
END
DECLARE @CurrentResults nVARCHAR(2000)
-- SELECT @CurrentResults = ISNULL(@CurrentResults + ',', '') + convert(varchar(200),[ID] )
-- FROM (select distinct id from #Results WHERE TmpID >= @FirstRec AND TmpID <= @LastRec) t
-- print @CurrentResultsIF @OrderDirection = 'D'
BEGIN
SELECT @CurrentResults = ISNULL(@CurrentResults + ',', '') + convert(varchar(200),[ID] )FROM #Results WHERE TmpID >= @FirstRec AND TmpID <= @LastRec
ORDER BY OrderByColumn DESC ;
ENDELSE
BEGIN
SELECT @CurrentResults = ISNULL(@CurrentResults + ',', '') + convert(varchar(200),[ID] )FROM #Results WHERE TmpID >= @FirstRec AND TmpID <= @LastRec
ORDER BY OrderByColumn ASC ;END
END
ELSE
-- MLS Listing Search
BEGIN
SELECT @CurrentResults = ISNULL(@CurrentResults + ',', '') + convert(varchar(200),[ID] )
FROM (select distinct id from #Results) t ;END
DECLARE
@ListIDs VARCHAR(8000) ;IF
@UseListingTempDB = 1BEGIN
-- Build a list of Listing IDs from the #List table
SELECT @ListIDs = ISNULL(@ListIDs + ',', '') + convert(varchar(20),[ListingID] )
FROM #List OPTION (KEEPFIXED PLAN);END
ELSE
SET @ListIDS = NULL ;EXEC dbo.sprIndividualSearchDetails @CurrentResults, @CultureID, @UseListingTempDB, @PublicSearch, @cobrandID, @ListIDs ;

keep fixed plan is there, yes to temp table, update stats are on for creating & updating automatically.
why exactly is this causing a recompile?
SELECT @ListIDs = ISNULL(@ListIDs + ',', '') + convert(varchar(20),[ListingID] )
FROM #List OPTION (KEEPFIXED PLAN);

Have you considered inserting the filter options into a table, then filtering by means of a join on that table, rather than building an excessively long WHERE clause?