Optimizing Stored Procedure by eliminating redundant 'SELECT' statement/query

Question

I've below Stored Procedure named GetFilteredArticles which returns 7 seperate tables for each day of a week i.e. Monday to Sunday

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

Note - day is a column name

So, in all, there will be 7 tables returning for each day. For this i would like to use SELECT Query at the minimal level, how should i do that..

Also I have 2 below options to remove redundancy -

1. Use While loop 7 times where day will be dyanamic
2. Static SQL Query N'' only day will be dyanmic

Expected- A single Select query which will return 7 tables for each day, instead of using 7 different select statements.

Any other suggestions or thoughts on this..?

Answer 1

Stored Procedures are methods and meant to do something. Functions are meant to read and deliver...

This is my suggestion:

I did not change anything in your query, just added the day column... There's quite some chance to improve...

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