SQL Server Performance

SQL:StmtRecompile

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by b_guru, Oct 19, 2007.

  1. b_guru New Member

    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);

  2. FrankKalis Moderator

    I don't think this is the complete procedure. Can you post the rest, too?
  3. b_guru New Member

    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 db:confused:rganization_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 db:confused:rganization 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 db:confused:RGANIZATION O WITH (NOLOCK) ON IO.ORGANIZATIONID = O.ORGANIZATIONID and IO.StatusID = 3
    LEFT JOIN db:confused:RGANIZATION_LOCALE OL WITH (NOLOCK) ON O.ORGANIZATIONID = OL.ORGANIZATIONID AND OL.CultureID = @CultureID
    LEFT OUTER JOIN db:confused:RGANIZATION_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 db:confused:RGANIZATION O2 WITH (NOLOCK) ON IO2.ORGANIZATIONID = O2.ORGANIZATIONID
    LEFT JOIN db:confused:RGANIZATION_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 ;
  4. b_guru New Member

    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 db:confused:rganization_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 db:confused:rganization 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 db:confused:RGANIZATION O WITH (NOLOCK) ON IO.ORGANIZATIONID = O.ORGANIZATIONID and IO.StatusID = 3
    LEFT JOIN db:confused:RGANIZATION_LOCALE OL WITH (NOLOCK) ON O.ORGANIZATIONID = OL.ORGANIZATIONID AND OL.CultureID = @CultureID
    LEFT OUTER JOIN db:confused:RGANIZATION_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 db:confused:RGANIZATION O2 WITH (NOLOCK) ON IO2.ORGANIZATIONID = O2.ORGANIZATIONID
    LEFT JOIN db:confused:RGANIZATION_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 ;
  5. satya Moderator

    • If the recompile occurred because a SET option changed, use SQL Server Profiler to determine which SET option changed. Avoid changing SET options within stored procedures. It is better to set them at the connection level. Ensure that SET options are not changed during the lifetime of the connection
    • Are you using temp.tables or using table variables?
    • Try using the KEEPFIXED PLAN query hint.
    • How often you perform UDPATE STATISTICS on involved tables?
    For additional information, see Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 (http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx) on Microsoft TechNet.
  6. b_guru New Member

    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);
  7. Adriaan New Member

    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?

Share This Page