text: 
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_NULLS ON
  GO
  CREATE PROCEDURE [dbo].[sp_Search_Results]
  	@UserEmail [varchar](60),
  	@KEYWORD_ID [int],
  	@MODULE_ID [char](1),
  	@PRIORITY_ID [int],
  	@ESTIMATE_ID [int],
  	@SYS_PRIORITY_ID [int],
  	@RELEASE_ID [int],
  	@FUNDER_ID [int],
  	@STATUS_ID [int],
  	@IncludeDeleted [bit],
  	@FULLTEXT [nvarchar](4000),
  	@CreatedOnOrAfter smalldatetime,
  	@ModifiedOnOrAfter smalldatetime
  WITH EXECUTE AS CALLER
  AS
  SET NOCOUNT ON
  
  /*
  	Checked for Release: 1.2
  	Checked by: KL
  	Checked on: 04-Jun-2013
  	Action: NO ACTION REQUIRED
  */
  
  DECLARE @USER_ID int
  SELECT @USER_ID=u.[USER_ID] FROM UserAccount u WHERE u.Email=@UserEmail
  
  DECLARE @NEUTRAL_PRIORITY smallint
  SELECT @NEUTRAL_PRIORITY=PRIORITY_ID FROM Priority WHERE PriorityCode='NEUTRAL'
  
  IF @FULLTEXT IS NOT NULL BEGIN
  	SET @FULLTEXT = RTRIM(LTRIM(@FULLTEXT))
  	IF @FULLTEXT <> '' BEGIN
  		SET @FULLTEXT = dbo.fn_normalize_fts(@FULLTEXT)
  	END ELSE BEGIN
  		SET @FULLTEXT = NULL
  	END
  END
  
  DECLARE @FullTextResults TABLE (
  	ID int
  )
  
  IF @FULLTEXT IS NOT NULL BEGIN
  	INSERT INTO @FullTextResults
  	SELECT ID FROM Enhancement WHERE CONTAINS(SRCH_Anywhere,@FULLTEXT)
  END
  
  SELECT
  	(SELECT k.Keyword FROM Keyword k WHERE k.KEYWORD_ID=@KEYWORD_ID) AS Keyword,
  	(SELECT m.ModuleName FROM Module m WHERE m.MODULE_ID=@MODULE_ID) AS Module,
  	(SELECT p.PriorityName FROM Priority p WHERE p.PRIORITY_ID=@PRIORITY_ID) AS UserPriority,
  	(SELECT p.PriorityName FROM Priority p WHERE p.PRIORITY_ID=@SYS_PRIORITY_ID) AS SysPriority,
  	(SELECT r.ReleaseName FROM Release r WHERE r.RELEASE_ID=@RELEASE_ID) AS Release,
  	CASE WHEN @FUNDER_ID=-1 THEN 'None' ELSE (SELECT f.FunderName FROM Funder f WHERE f.FUNDER_ID=@FUNDER_ID) END AS Funder,
  	(SELECT s.StatusName FROM Status s WHERE s.STATUS_ID=@STATUS_ID) AS Status,
  	(SELECT ISNULL('$' + CAST(es.CostLow AS varchar) + ' - $' + CAST(es.CostHigh AS varchar),EstimateCode) FROM Estimate es WHERE es.ESTIMATE_ID=@ESTIMATE_ID) AS CostRange
  
  SELECT PRIORITY_ID, PriorityCode, PriorityName, [Weight] FROM Priority ORDER BY [Weight] DESC
  
  SELECT e.ID,
  	e.Title,
  	CAST(CASE WHEN EXISTS(SELECT * FROM EnhancementModule em WHERE em.ENHANCEMENT_ID=e.ID AND em.MODULE_ID='C') THEN 1 ELSE 0 END AS bit) AS CIC,
  	CAST(CASE WHEN EXISTS(SELECT * FROM EnhancementModule em WHERE em.ENHANCEMENT_ID=e.ID AND em.MODULE_ID='V') THEN 1 ELSE 0 END AS bit) AS VOL,
  	CAST(CASE WHEN EXISTS(SELECT * FROM EnhancementModule em WHERE em.ENHANCEMENT_ID=e.ID AND em.MODULE_ID='T') THEN 1 ELSE 0 END AS bit) AS TRACKER,
  	CAST(CASE WHEN EXISTS(SELECT * FROM EnhancementModule em WHERE em.ENHANCEMENT_ID=e.ID AND em.MODULE_ID='E') THEN 1 ELSE 0 END AS bit) AS ENHANCEMENT,
  	CAST(CASE WHEN EXISTS(SELECT * FROM EnhancementModule em WHERE em.ENHANCEMENT_ID=e.ID AND em.MODULE_ID='O') THEN 1 ELSE 0 END AS bit) AS [OFFLINE],
  	CAST(CASE WHEN EXISTS(SELECT * FROM EnhancementModule em WHERE em.ENHANCEMENT_ID=e.ID AND em.MODULE_ID='R') THEN 1 ELSE 0 END AS bit) AS COMMUNITY,
  	LEFT(e.BasicDescription,220) + CASE WHEN LEN(BasicDescription) > 220 THEN '...' ELSE '' END AS ShortDescription,
  	dbo.fn_DateString(e.MODIFIED_DATE) AS LastModified,
  	(SELECT f.FunderName FROM Funder f WHERE f.FUNDER_ID=e.SYS_FUNDER) AS Funder,
  	s.StatusName AS [Status],
  	dbo.fn_EnhancementToModule(e.ID) AS Modules,
  	p.PRIORITY_ID AS SysPriority,
  	(SELECT TOP 1 p.PRIORITY_ID FROM Priority p
  		WHERE EXISTS(SELECT * FROM UserEnhancementPriority uep
  			WHERE p.PRIORITY_ID=uep.PRIORITY_ID
  				AND uep.ENHANCEMENT_ID=e.ID
  				AND uep.[USER_ID]=@USER_ID)
  		OR p.PRIORITY_ID=@NEUTRAL_PRIORITY
  		ORDER BY CASE WHEN p.PRIORITY_ID=@NEUTRAL_PRIORITY THEN 1 ELSE 0 END
  	) AS UserPriority,
  	ISNULL('$' + CAST(es.CostLow AS varchar) + ' - $' + CAST(es.CostHigh AS varchar),EstimateCode) AS CostRange,
  	STUFF(
         (SELECT
              N', ' + r.ReleaseName
              FROM Release r
              INNER JOIN EnhancementRelease er
  				ON r.RELEASE_ID=er.RELEASE_ID AND er.ENHANCEMENT_ID=e.ID
              ORDER BY r.ReleaseName
              FOR XML PATH(''), TYPE
         ).value('./text()[1]','nvarchar(max)')
         ,1,2, ''
      ) AS Releases
  	FROM Enhancement e
  	INNER JOIN Estimate es
  		ON e.SYS_ESTIMATE=es.ESTIMATE_ID
  	LEFT JOIN Priority p
  		ON e.SYS_PRIORITY=p.PRIORITY_ID
  	INNER JOIN [Status] s
  		ON e.SYS_STATUS=s.STATUS_ID AND (@IncludeDeleted=1 OR s.ShowByDefault=1)
  WHERE (@KEYWORD_ID IS NULL OR EXISTS(SELECT * FROM EnhancementKeyword WHERE ENHANCEMENT_ID=e.ID AND KEYWORD_ID=@KEYWORD_ID))
  	AND (@MODULE_ID IS NULL OR EXISTS(SELECT * FROM EnhancementModule WHERE ENHANCEMENT_ID=e.ID AND MODULE_ID=@MODULE_ID))
  	AND (@PRIORITY_ID IS NULL 
  			OR EXISTS(SELECT * FROM UserEnhancementPriority WHERE [USER_ID]=@USER_ID AND ENHANCEMENT_ID=e.ID AND PRIORITY_ID=@PRIORITY_ID)
  			OR (@PRIORITY_ID=@NEUTRAL_PRIORITY AND NOT EXISTS(SELECT * FROM UserEnhancementPriority WHERE [USER_ID]=@USER_ID AND ENHANCEMENT_ID=e.ID))
  		)
  	AND (@SYS_PRIORITY_ID IS NULL OR SYS_PRIORITY=@SYS_PRIORITY_ID OR (SYS_PRIORITY IS NULL AND @SYS_PRIORITY_ID=@NEUTRAL_PRIORITY))
  	AND (@ESTIMATE_ID IS NULL OR SYS_ESTIMATE=@ESTIMATE_ID)
  	AND (@FULLTEXT IS NULL OR EXISTS(SELECT * FROM @FullTextResults WHERE ID=e.ID))
  	AND (@RELEASE_ID IS NULL OR EXISTS(SELECT * FROM EnhancementRelease WHERE ENHANCEMENT_ID=e.ID AND RELEASE_ID=@RELEASE_ID))
  	AND (@FUNDER_ID IS NULL OR e.SYS_FUNDER=@FUNDER_ID OR (@FUNDER_ID=-1 AND e.SYS_FUNDER IS NULL))
  	AND (@STATUS_ID IS NULL OR e.SYS_STATUS=@STATUS_ID)
  	AND (@CreatedOnOrAfter IS NULL OR e.CREATED_DATE >= @CreatedOnOrAfter)
  	AND (@ModifiedOnOrAfter IS NULL OR e.MODIFIED_DATE >= @ModifiedOnOrAfter)
  ORDER BY p.Weight DESC, e.ID
  
  IF @UserEmail IS NOT NULL BEGIN
  	EXEC sp_User_Priorities @UserEmail
  	EXEC sp_User_Cart @UserEmail
  END
  SET NOCOUNT OFF
  
  
  
  GO
  
  
  GRANT EXECUTE ON  [dbo].[sp_Search_Results] TO [web_user_role]
  GO
  