USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetCAMSearch]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
-- Author:		Magic
-- Create date: 08-Mar-2011
-- Description:	Get the result based on the keyword search
-- =============================================================


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCAMSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCAMSearch]
GO
CREATE PROCEDURE [dbo].[GetCAMSearch]
	-- Add the parameters for the stored procedure here
	@sSearchKeyword nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT COUNT(DISTINCT CAMWeightage.Keyword) AS hitcount, 
    SUM(CAMWeightage.Relevancy) AS score, 
    CAMMetadata.Title, CAMMetadata.ProjectTypeId, CAMMetadata.GenContentId, 
    CAMMetadata.SubContent
	FROM CAMMetadata
	INNER JOIN CAMWeightage 
	ON CAMMetadata.Id = CAMWeightage.MetadataId
	WHERE CAMMetadata.LexiconId = 1
	AND CAMWeightage.Keyword = @sSearchKeyword
	GROUP BY CAMWeightage.Keyword, CAMWeightage.Relevancy, CAMMetadata.Title, 
	CAMMetadata.ProjectTypeId, CAMMetadata.GenContentId,CAMMetadata.SubContent
	UNION
	SELECT 1 AS hitcount, 1 AS score, CAMMetadata.Title, CAMMetadata.ProjectTypeId, 
	CAMMetadata.GenContentId, CAMMetadata.SubContent 
    FROM CAMMetadata WHERE (' ' + CAMMetadata.Title+ ' ') LIKE '%'+ @sSearchKeyword +'%'
	AND CAMMetadata.Id NOT IN 
	(SELECT CAMWeightage.MetadataId FROM CAMWeightage WHERE CAMWeightage.Keyword = @sSearchKeyword)
	ORDER BY hitcount DESC, score DESC	

END

GO