title: Cannot output correct rows from DB after using an IF statement
body: <p>I am trying to create a tsql stored procedure which will output rows based on some parameters. The ultimate aim is to move the rows to another table which is why I have counts - I want to keep track of how many rows I am moving. There are two tables - <code>Notes</code> and <code>ExtraNotes</code>. The <code>ExtraNotes</code> holds overflow info from the first table. </p>
  
  <p>I am using the <code>if</code> statement to select the correct rows based on the parameter called <code>NoteType</code> but I don't know how to output the correct select statement within the <code>if</code>. I know the select statement within each <code>if</code> is wrong </p>
  
  <pre><code>select * 
  from dbo.Notes 
  left join dbo.ExtraNotes on Notes.NoteID = dbo.ExtraNotes.NoteID 
  where NoteDate &lt;= @Date
  </code></pre>
  
  <p>Could anyone offer some pointers on how I can output the correct rows and possibly restructure this better? </p>
  
  <p>The full code is here.</p>
  
  <pre><code>alter proc selectrows 
     --external variables
     @Date datetime,
     @NoteType varchar(2)
  as
  --internal variables
  --Count variables, before any changes
  declare @count_rowsBefore int
  declare @count_Extra_rowsBefore int
  
  --Count variables of selected rows to be moved
  
  declare @count_SelectedRows int
  declare @count_Extra_SelectedRows int
  
  select @count_rowsBefore = count(*)
  from dbo.Notes
  
  select @count_Extra_SelectedRows = count(*)
  from dbo.ExtraNotes
  
  
  if(@NoteType= 'B')
  begin
  select @count_SelectedRows = count(dbo.Notes.NoteID), @count_Extra_SelectedRows =            count(dbo.ExtraNotes.NoteID)
          from dbo.Notes
          left join dbo.ExtraNotes on
          Notes.NoteID = dbo.ExtraNotes.NoteID
          where NoteDate &lt;= @Date
  
          select *
          from dbo.Notes
          left join dbo.ExtraNotes on
          Notes.NoteID = dbo.ExtraNotes.NoteID
          where NoteDate &lt;= @Date
  end
  else if(@NoteType = 'S')
    begin
    select @count_SelectedRows = count(dbo.Notes.NoteID), 
  @count_Extra_SelectedRows = count(dbo.ExtraNotes.NoteID)
          from dbo.Notes
          left join dbo.ExtraNotes on
          Notes.NoteID = dbo.ExtraNotes.NoteID
          where NoteDate &lt;= @Date
          and NoteType = 'S'
  
          select *
          from dbo.Notes
          left join dbo.ExtraNotes on
          Notes.NoteID = dbo.ExtraNotes.NoteID
          where NoteDate &lt;= @Date
  end
  else if (@NoteType = 'M')
  begin
  select @count_SelectedRows = count(dbo.Notes.NoteID),
  @count_Extra_SelectedRows =  count(dbo.ExtraNotes.NoteID)
          from dbo.Notes
          left join dbo.ExtraNotes on
          Notes.NoteID = dbo.ExtraNotes.NoteID
          where NoteDate &lt;= @Date
          and NoteType = 'M'
  
          select *
          from dbo.Notes
          left join dbo.ExtraNotes on
          Notes.NoteID = dbo.ExtraNotes.NoteID
          where NoteDate &lt;= @Date
    end
   else
    begin
    raiserror('Please enter a valid Note Read Type= M, S or B',16,1)
    end
  
  Print 'Total Number of rows: ' + cast(@count_rowsBefore as varchar(10))
  Print 'Total Number of "Extra" rows: ' + cast(@count_Extra_RowsBefore as varchar(10))
  
  Print '-----------------------------------------------'
  Print 'Total Number of rows to Move: ' + cast(@count_SelectedRows as varchar(10))
  Print 'Total Number of "Extra" Rows to Move: ' + cast(@count_Extra_SelectedRows 
  as varchar(10))
  </code></pre>
  
  <p><a href="http://i2.photobucket.com/albums/y19/_karma_/code/ouput.png" rel="nofollow">Output screenshot</a> </p>