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

Enjoy!

(No Ratings Yet)

Loading...

This entry (Permalink) was posted on Tuesday, July 7th, 2009 at 12:15 pm and is filed under Code Samples, EPiCode, EPiServer, Microsoft SqlServer, Software Development, Solution, SQL, Web applications. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response , or trackback from your own site.