title: Optimizing Stored Procedure by eliminating redundant 'SELECT' statement/query
body: <p>I've below <code>Stored Procedure</code> named <code>GetFilteredArticles</code> which returns <code>7 seperate tables</code> for each <code>day of a week</code> <code>i.e. Monday to Sunday</code></p>
  
  <pre><code>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 &lt;--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 &lt;--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 &lt;--Condition [where day='Wednesday']                                                                                                                               
  order by ISNULL(SortOrder,0) asc
  .....
  .....
  .....
  ..... and so on for rest of the days...
  END
  </code></pre>
  
  <p><strong>Note</strong> - <code>day is a column name</code></p>
  
  <p>So, in all, there will be <code>7 tables</code> returning for each day.
  For this i would like to use <code>SELECT Query</code> at the minimal level, how should i do that..</p>
  
  <p>Also I have <code>2</code> below options to <code>remove redundancy</code> -</p>
  
  <ol>
  <li>Use <code>While loop</code> 7 times where <code>day will be dyanamic</code></li>
  <li><code>Static SQL Query N'</code>' only day will be dyanmic</li>
  </ol>
  
  <p><strong>Expected</strong>- A <code>single Select query</code> which will <code>return 7 tables</code> for <code>each day</code>, <code>instead</code> of using <code>7</code> different <code>select statements</code>.</p>
  
  <p>Any other <code>suggestions or thoughts</code> on this..?  </p>