Create PROC DBO.Article_SelectByCriteria_BySqlPaging
-- Parameters of Article_SelectByCriteria--------------
@P_GUID VARCHAR(40)='' , -- OPTIONAL
@P_parentArticleId INT = 0 , --OPTIONAL
@P_postingURL nVARCHAR(200) = '' , --OPTIONAL
@P_authorId INT = 0, --OPTIONAL
@P_elaphObjectId INT =0, --OPTIONAL
@P_assignedBy BIGINT=0, --OPTIONAL
@P_assignedTo BIGINT= 0 , --OPTIONAL
@P_assignedDate VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_publishedBy BIGINT = 0, -- OPTIONAL
@P_publishedDate VARCHAR(50) = '12:00:00 AM' ,-- OPTIONAL
@P_lastModifiedBY BIGINT = 0 , -- OPTIONAL
@P_lastModifiedDate VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_archivedBy BIGINT = 0, --OPTIONAL
@P_archivedDate VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_expirationDate VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_CountryId TINYINT = 0 , --OPTIONAL
@P_IsSectionPageNewsTicker BIT = 0 , --OPTIONAL
@P_isHomePageNewsTicker BIT = 0 , --OPTIONAL
@P_isPrinterFriendly BIT = 0 , --OPTIONAL
@P_isSendToFriend BIT = 0 , --OPTIONAL
@P_isAddToFavorite BIT = 0, --OPTIONAL
@P_isWriteToAuthor BIT = 0 , --OPTIONAL
@P_isSubscribedArticle BIT = 0 , --OPTIONAL
@P_isDisablePrintCutPaste BIT = 0, --OPTIONAL
@P_IsCopyRight BIT = 0 , --OPTIONAL
@P_IsArticleRating BIT = 0 , --OPTIONAL
@P_IsComments BIT = 0 , --OPTIONAL
@P_articleStatusId INT = 0, --OPTIONAL
@P_articleTypeId TINYINT = 0 ,--OPTIONAL
@P_createdBy BIGINT = 0 , --OPTIONAL
@PageSize int, @PageNumber int
WITH RECOMPILE
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max), -- SQL statement to execute
@ubound int
Declare @RowStart int
Declare @RowEnd int
if @PageNumber > 0
Begin
SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
--IF @start < 1 SET @start = 1
--IF @maxct < 1 SET @maxct = 1
-- SET @ubound = @start + @maxct
DECLARE @L_WhereClause VARCHAR(8000)
DECLARE @L_SQL Varchar(8000)
SET @L_WhereClause = ''
SET @L_SQL = ''
If @P_parentArticleId <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.ParentArticleId =' + CAST(@P_parentArticleId AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.ParentArticleId =' + CAST(@P_parentArticleId AS VARCHAR(20))
END
IF @P_GUID <> ''
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.GUID =' + ''''+ @P_GUID + ''''
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.GUID =' + ''''+ @P_GUID + ''''
END
IF @P_postingURL <> ''
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.PostingURL =' + ''''+ @P_postingURL + ''''
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.PostingURL =' + ''''+ @P_postingURL + ''''
END
If @P_authorId <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.AuthorId =' + CAST(@P_authorId AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.AuthorId =' + CAST(@P_authorId AS VARCHAR(20))
END
If @P_elaphObjectId <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.ElaphObjectId =' + CAST(@P_elaphObjectId AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.ElaphObjectId =' + CAST(@P_elaphObjectId AS VARCHAR(20))
END
If @P_assignedBy <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.AssignedBy =' + CAST(@P_assignedBy AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.AssignedBy =' + CAST(@P_assignedBy AS VARCHAR(20))
END
If @P_assignedTo <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.AssignedTo =' + CAST(@P_assignedTo AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.AssignedTo =' + CAST(@P_assignedTo AS VARCHAR(20))
END
If @P_assignedDate <> '12:00:00 AM'
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.AssignedDate =' + '''' + @P_assignedDate + ''''
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.AssignedDate =' + '''' + @P_assignedDate + ''''
END
If @P_publishedBy <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.PublishedBy =' + CAST(@P_publishedBy AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.PublishedBy =' + CAST(@P_publishedBy AS VARCHAR(20))
END
-- If @P_publishedDate <> '12:00:00 AM'
-- BEGIN
-- IF @L_WhereClause <> ''
-- SET @L_WhereClause = @L_WhereClause + ' AND Article.PublishedDate =' + '''' + @P_publishedDate + ''''
-- ELSE
-- SET @L_WhereClause = @L_WhereClause + ' Article.PublishedDate =' + '''' + @P_publishedDate + ''''
-- END
If @P_lastModifiedBY <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedBY =' + CAST(@P_lastModifiedBY AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.LastModifiedBY =' + CAST(@P_lastModifiedBY AS VARCHAR(20))
END
If @P_lastModifiedDate <> '12:00:00 AM'
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedDate =' + '''' + @P_lastModifiedDate + ''''
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.LastModifiedDate =' + '''' + @P_lastModifiedDate + ''''
END
If @P_archivedBy <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedBy =' + CAST(@P_archivedBy AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.ArchivedBy =' + CAST(@P_archivedBy AS VARCHAR(20))
END
If @P_archivedDate <> '12:00:00 AM'
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedDate =' + '''' + @P_archivedDate + ''''
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.ArchivedDate =' + '''' + @P_archivedDate + ''''
END
If @P_expirationDate <> '12:00:00 AM'
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.ExpirationDate =' + '''' + @P_expirationDate + ''''
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.ExpirationDate =' + '''' + @P_expirationDate + ''''
END
If @P_CountryId <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.CountryId =' + CAST(@P_CountryId AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.CountryId =' + CAST(@P_CountryId AS VARCHAR(20))
END
If @P_IsSectionPageNewsTicker <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.IsSectionPageNewsTicker =' + CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.IsSectionPageNewsTicker =' + CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))
END
If @P_isHomePageNewsTicker <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.IsHomePageNewsTicker =' + CAST(@P_isHomePageNewsTicker AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.IsHomePageNewsTicker =' + CAST(@P_isHomePageNewsTicker AS VARCHAR(20))
END
IF @P_isPrinterFriendly <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.isPrinterFriendly =' + CAST(@P_isPrinterFriendly AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.isPrinterFriendly =' + CAST(@P_isPrinterFriendly AS VARCHAR(20))
END
IF @P_isSendToFriend <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.isSendToFriend =' + CAST(@P_isSendToFriend AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.isSendToFriend =' + CAST(@P_isSendToFriend AS VARCHAR(20))
END
IF @P_isAddToFavorite <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.IsAddToFavorite =' + CAST(@P_isAddToFavorite AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.IsAddToFavorite =' + CAST(@P_isAddToFavorite AS VARCHAR(20))
END
IF @P_isWriteToAuthor <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.IsWriteToAuthor =' + CAST(@P_isWriteToAuthor AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.IsWriteToAuthor =' + CAST(@P_isWriteToAuthor AS VARCHAR(20))
END
IF @P_isSubscribedArticle <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.IsSubscribedArticle =' + CAST(@P_isSubscribedArticle AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.IsSubscribedArticle =' + CAST(@P_isSubscribedArticle AS VARCHAR(20))
END
IF @P_isDisablePrintCutPaste <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.IsDisablePrintCutPaste =' + CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.IsDisablePrintCutPaste =' + CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))
END
IF @P_IsCopyRight <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.IsCopyRight =' + CAST(@P_IsCopyRight AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.IsCopyRight =' + CAST(@P_IsCopyRight AS VARCHAR(20))
END
IF @P_IsArticleRating <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.IsArticleRating =' + CAST(@P_IsArticleRating AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.IsArticleRating =' + CAST(@P_IsArticleRating AS VARCHAR(20))
END
IF @P_IsComments <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.IsComments =' + CAST(@P_IsComments AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.IsComments =' + CAST(@P_IsComments AS VARCHAR(20))
END
IF @P_articleStatusId <> 0
BEGIN
IF @P_articleStatusId = 1
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' + CAST(@P_articleStatusId AS VARCHAR(20)) + ' AND Article.PublishedDate <= ' + '''' + CAST(getdate() AS VARCHAR(50)) + '''' + ' AND Article.expirationDate > ' + '''' +
CAST(getdate() AS VARCHAR(50)) + ''''
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' + CAST(@P_articleStatusId AS VARCHAR(20)) + ' AND Article.PublishedDate <= ' + '''' + CAST(getdate() AS VARCHAR(50)) + '''' + ' AND Article.expirationDate > ' + '''' + CAST(getdate() AS VARCHAR(50)) + ''''
END
ELSE
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' + CAST(@P_articleStatusId AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' + CAST(@P_articleStatusId AS VARCHAR(20))
END
END
IF @P_articleTypeId <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.ArticleTypeId =' + CAST(@P_articleTypeId AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.ArticleTypeId =' + CAST(@P_articleTypeId AS VARCHAR(20))
END
IF @P_createdBy <> 0
BEGIN
IF @L_WhereClause <> ''
SET @L_WhereClause = @L_WhereClause + ' AND Article.CreatedBy =' + CAST(@P_createdBy AS VARCHAR(20))
ELSE
SET @L_WhereClause = @L_WhereClause + ' Article.CreatedBy =' + CAST(@P_createdBy AS VARCHAR(20))
END
IF @L_WhereClause = ''
SET @L_WhereClause = 'where row >= ' + CONVERT(varchar(9), @RowStart ) + ' AND
row <= ' + CONVERT(varchar(9),@RowEnd)
SET @STMT = ' SELECT TOP 500 *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY PublishedDate Desc) AS row, ArticleId, PublishedDate
FROM Article
) AS tbl
'+@L_WhereClause+' '
EXEC (@STMT)
end
GO
//// Now You can execute the SP
Article_SelectByCriteria_BySqlPaging
@PageSize = 15,
@PageNumber = 11
No comments:
Post a Comment