我在下面Stored Procedure命名为GetFilteredArticles，它为每个day of a week i.e. Monday to Sunday返回7 seperate tables
CREATE Procedure [dbo].[GetFilteredArticles]
@UserName NVARCHAR(100),
AS
BEGIN
SET NOCOUNT ON;
[**Returns table for Monday**]
SELECT PostID,Title,convert(char(10),PostDate,101) as PostDate,IsEntertainment, ISNULL(DrPick ,0) as DrPick,
ImageURL,ISNULL(MustPick ,0) as MustPick ,ISNULL(Lead ,0) as Lead
,ISNULL(NonLead,0) as NonLead , convert(char(10),CreationDate,101) as CreationDate,ISNULL(TBW,0) as TBW,
SortOrder,NLDate,[Day], PostURL, Direction,ISNULL(IsArticleLevelPost,0) as IsArticleLevelPost ,Sources, HeadLine,
ISNULL(PromoteTo,0) as PromoteTo,ParentID, PostLevel, IsHLTestPassed, Synapsis, ProjectedViews,ISNULL([SaveChecked],0) as [SaveChecked]
FROM PromoteToArticleNLPrep
where day='Monday' and UserName = @UserName <--Condition [where day='Monday']
order by ISNULL(SortOrder,0) asc
[**Returns table for Tuesday**]
SELECT PostID,Title,convert(char(10),PostDate,101) as PostDate,IsEntertainment, ISNULL(DrPick ,0) as DrPick,
ImageURL,ISNULL(MustPick ,0) as MustPick ,ISNULL(Lead ,0) as Lead
,ISNULL(NonLead,0) as NonLead , convert(char(10),CreationDate,101) as CreationDate,ISNULL(TBW,0) as TBW,
SortOrder,NLDate,[Day], PostURL, Direction , ISNULL(IsArticleLevelPost,0) as IsArticleLevelPost,Sources, HeadLine ,
ISNULL(PromoteTo,0) as PromoteTo,ParentID, PostLevel, IsHLTestPassed, Synapsis, ProjectedViews,ISNULL([SaveChecked],0) as [SaveChecked]
FROM PromoteToArticleNLPrep
where day='Tuesday' and UserName = @UserName <--Condition [where day='Tuesday']
order by ISNULL(SortOrder,0) asc
[**Returns table for Wednesday**]
SELECT PostID,Title,convert(char(10),PostDate,101) as PostDate,IsEntertainment, ISNULL(DrPick ,0) as DrPick,
ImageURL,ISNULL(MustPick ,0) as MustPick ,ISNULL(Lead ,0) as Lead
,ISNULL(NonLead,0) as NonLead , convert(char(10),CreationDate,101) as CreationDate,ISNULL(TBW,0) as TBW,
SortOrder,NLDate,[Day], PostURL, Direction , ISNULL(IsArticleLevelPost,0) as IsArticleLevelPost,Sources, HeadLine ,
ISNULL(PromoteTo,0) as PromoteTo,ParentID, PostLevel, IsHLTestPassed, Synapsis, ProjectedViews,ISNULL([SaveChecked],0) as [SaveChecked]
FROM PromoteToArticleNLPrep
where day='Wednesday' and UserName = @UserName <--Condition [where day='Wednesday']
order by ISNULL(SortOrder,0) asc
.....
.....
.....
..... and so on for rest of the days...
END
注意 - day is a column name
，每天都会有7 tables返回。为此，我想在最低级别使用SELECT Query，我应该怎么做..
我还有以下2选项remove redundancy -
While loop使用 7 次，day will be dyanamic-
Static SQL Query N'' 唯一的一天将是 dyanmic
预期 - 一个single Select query，它将return 7 tables each day，instead使用7不同的select statements。
还有其他suggestions or thoughts..？
存储过程是用于执行某些操作的方法。函数旨在读取和交付...
这是我的建议：
我没有在您的查询中更改任何内容，只是添加了日期列...有相当多的改进机会...
CREATE FUNCTION dbo.GetFilteredArticles
(
@UserName VARCHAR(150)=NULL
,@WeekDay VARCHAR(30)=NULL
)
RETURNS TABLE
AS
RETURN
SELECT day, PostID,Title,convert(char(10),PostDate,101) as PostDate,IsEntertainment, ISNULL(DrPick ,0) as DrPick,
ImageURL,ISNULL(MustPick ,0) as MustPick ,ISNULL(Lead ,0) as Lead
,ISNULL(NonLead,0) as NonLead , convert(char(10),CreationDate,101) as CreationDate,ISNULL(TBW,0) as TBW,
SortOrder,NLDate,[Day], PostURL, Direction,ISNULL(IsArticleLevelPost,0) as IsArticleLevelPost ,Sources, HeadLine,
ISNULL(PromoteTo,0) as PromoteTo,ParentID, PostLevel, IsHLTestPassed, Synapsis, ProjectedViews,ISNULL([SaveChecked],0) as [SaveChecked]
FROM PromoteToArticleNLPrep
where @WeekDay IS NULL OR day=@WeekDay
and UserName = @UserName;
GO
--You might call this like this: You'll get all rows sorted
SELECT * FROM dbo.GetFilteredArticles(NULL,'Pranav')
ORDER BY day, CASE WHEN SortOrder IS NULL THEN 0 ELSE SortOrder END asc
--or for one day
SELECT * FROM dbo.GetFilteredArticles('Tuesday','Pranav')
ORDER BY day, CASE WHEN SortOrder IS NULL THEN 0 ELSE SortOrder END asc