USE [AIADatabaseV5] GO /****** Object: StoredProcedure [dbo].[GetSearchTerms] Script Date: 2/1/2018 12:15:55 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: magic -- Create date: 19/5/2009 -- Description: Fetch Editions for building level accounts for given Account Number and SiteId. -- ============================================= if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSearchTerms]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[GetSearchTerms] GO CREATE PROCEDURE [dbo].[GetSearchTerms] AS BEGIN IF 1=0 BEGIN SET FMTONLY OFF END -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here Select * from ( Select CASE WHEN trm.SearchText is not null THEN trm.SearchText ELSE phr.SearchText END as SearchText, isnull(trm.TID,0) as VocabTermId, isnull(phr.PID,0) as PhraseId, (CASE WHEN trm.LID IS NOT NULL THEN trm.LID ELSE phr.LID END) as LexiconId From (Select vt.TermText + 'T' as SearchText,max(vtn.TermNumber) as TID, vt.VocabLexiconId as LID From dbo.VocabTerms vt INNER JOIN dbo.VocabTermNumbers vtn on vt.TermNumber=vtn.TermNumber INNER JOIN dbo.VocabLexicon vl on vt.VocabLexiconId = vl.Id Where vtn.IsCurrent='Y' and vl.IsCurrent = 'Y' Group by vt.TermText + 'T', vt.VocabLexiconId ) trm FULL OUTER JOIN (Select sp.Phrase + 'T' as SearchText, max(sp.Id) as PID, 1 as LID From ContentDescriptor cd INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.Id=cdpm.ContentDescriptorId INNER JOIN dbo.SearchPhrase sp on cdpm.SearchPhraseId=sp.Id Group by sp.Phrase + 'T' Union Select sp.Phrase + 'T' as SearchText, max(sp.Id) as PID, 2 as LID From ContentDescriptor cd INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.Id=cdpm.ContentDescriptorId INNER JOIN dbo.SearchPhrase sp on cdpm.SearchPhraseId=sp.Id Group by sp.Phrase + 'T') phr ON trm.SearchText = phr.SearchText and trm.LID = phr.LID) ads Order by LexiconId,SearchText END GO