|
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}grmGetRepositoryObjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmGetRepositoryObjects]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE procedure {databaseOwner}{objectQualifier}grmGetRepositoryObjects
(
@ModuleID int,
@sFilter nvarchar(256),
@sSort nvarchar(100),
@Approved int,
@CategoryId int,
@Attributes nvarchar(150),
@RowCount int
)
AS
BEGIN
IF @RowCount > 0
SET ROWCOUNT @RowCount
END
DECLARE @sql NVARCHAR(4000)
DECLARE @SearchToken nvarchar(128)
DECLARE @SearchClause nvarchar(50)
BEGIN
SELECT @sql = 'SELECT a.*, (SELECT COUNT({databaseOwner}{objectQualifier}grmRepositoryComments.ItemID) FROM {databaseOwner}{objectQualifier}grmRepositoryComments ' +
'WHERE {databaseOwner}{objectQualifier}grmRepositoryComments.ObjectID=a.ItemID) AS CommentCount '
IF @CategoryId = -1
BEGIN
SET @sql = @sql + 'FROM {databaseOwner}{objectQualifier}grmRepositoryObjects a '
END
ELSE
BEGIN
SET @sql = @sql + 'FROM {databaseOwner}{objectQualifier}grmRepositoryObjects a, {databaseOwner}{objectQualifier}grmRepositoryObjectCategories b '
END
SET @sql = @sql +
'WHERE a.ModuleID = ' + CONVERT(VARCHAR, @ModuleID) + ' ' +
'AND ( CONVERT(VARCHAR,a.Approved) = ' + CONVERT(VARCHAR, @Approved) + ') ' +
'AND ( ''' + @Attributes + ''' = '''' OR {databaseOwner}{objectQualifier}grmCheckAllAttributes(''' + @Attributes + ''','';'', a.ItemID) = 1) '
IF @CategoryId <> -1
BEGIN
SET @sql = @sql + 'AND (a.ItemID = b.ObjectID AND b.CategoryId = ' + CONVERT(VARCHAR, @CategoryId) + ') '
END
IF @sFilter <> ''
BEGIN
SET @sql = @sql + 'AND ( '
IF LOWER(SUBSTRING(@sFilter, 1, 8)) = 'article='
BEGIN
SET @sql = @sql + 'a.ItemID = ' + SUBSTRING(@sFilter, 9, LEN(@sFilter) - 8)
END
ELSE
BEGIN
-- inject a set of search filters for each search word or phrase
DECLARE TCUR CURSOR FOR
SELECT [Value] FROM {databaseOwner}{objectQualifier}grmParseDelimitedStrings(@sFilter, '|')
SET @SearchClause = 'false'
OPEN TCUR
FETCH NEXT FROM TCUR INTO @SearchToken
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SearchClause <> 'true'
BEGIN
SET @SearchClause = 'true'
SET @sql = @sql +
'(( a.Description like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.Summary like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
' OR ( a.Name like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.FileName like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
' OR ( a.Author like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.AuthorEmail like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
' ) '
END
ELSE
BEGIN
SET @sql = @sql +
' OR (( a.Description like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.Summary like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
' OR ( a.Name like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.FileName like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
' OR ( a.Author like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.AuthorEmail like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
' ) '
END
FETCH NEXT FROM TCUR INTO @SearchToken
END
CLOSE TCUR
DEALLOCATE TCUR
END
SET @sql = @sql + ' ) '
END
IF @sSort = 'Name'
BEGIN
SET @sql = @sql + 'ORDER BY a.Name ASC '
END
IF @sSort = 'Author'
BEGIN
SET @sql = @sql + 'ORDER BY a.Author ASC '
END
IF @sSort = 'Downloads'
BEGIN
SET @sql = @sql + 'ORDER BY a.Downloads DESC '
END
IF @sSort = 'RatingAverage'
BEGIN
SET @sql = @sql + 'ORDER BY a.RatingAverage DESC '
END
IF @sSort = 'UpdatedDate'
BEGIN
SET @sql = @sql + 'ORDER BY a.UpdatedDate DESC '
END
EXEC(@sql)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|