Aparently I have written something on that note before for CMS 4 and it looks like someone still needs it as I got a request for an updated version for it a couple of days ago. So here we go:
for the most part the syntax for the call is equivalent to what is was before so go to my previous article regarding that (check out the old article for details). What I’ve added this time around is:
- the @PropertyName can be declared as ‘%’ if you want to look in all property names
- @PropertyType can be –1 if you want to look in all property types otherwise you need to specify type id (this has changed from type name before due to database schema changes)
- additionally this version of the stored proc will only look in the Master language Branch, so it will work for the single language pages and for multi-language but for language agnostic properties. (should you require the language to be variable the change is pretty simple – I can send you the updated version by email.
/****** Object: StoredProcedure [dbo].[PagedSearch] Script Date: 07/07/2009 12:18:10 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PagedSearch]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[PagedSearch] GO CREATE Procedure PagedSearch @Condition varchar(1024), @PropertyName varchar(1024), @PropertyType int, @PageSize int, @PageNumber int, @Offset int AS BEGIN DECLARE @RowStart int DECLARE @RowEnd int SET @RowStart = @PageSize * @PageNumber + @Offset; SET @RowEnd = @RowStart + @PageSize + @Offset; WITH PageRefs AS (SELECT page.pkID as PageId, ROW_NUMBER() OVER (ORDER BY pageLang.StartPublish DESC) as RowNumber FROM tblPage page, tblProperty propValue, tblPageDefinition propDef, tblPageLanguage pageLang WHERE page.pkID = propValue.fkPageID AND page.fkMasterLanguageBranchID = pageLang.fkLanguageBranchID AND page.pkID = pageLang.fkPageID AND propValue.fkPageDefinitionID = propDef.pkID AND (@propertyType = -1 or propDef.fkPageDefinitionTypeID = @propertyType) -- is proper type AND propDef.Searchable = 1 -- the property is searchable AND propValue.String like @Condition -- contains facets AND propDef.[Name] like @PropertyName) -- property of proper name SELECT PageId FROM PageRefs WHERE (RowNumber Between @RowStart and @RowEnd) or (@PageSize = 0); END GO
However… looking how the schema has changed over time, I am not convinced this approach is really the best one for someone who is not prepared to deal with the changes (e.g. you better be able to change the stored procedure based on the schema changes – or bribe me with pizza and beers for updates :) ).
Additionally this procedure only searches for properties that store their value in Short string field. To make it look into long string you need to Change the highlighted line to.
AND (propValue.LongString like @Condition)
or alternatively to look in both change it to:
AND ((propValue.String like @Condition) or (propValue.LongString like @Condition))