title: Insert into different tables depending on parameter value
body: <p>I have a stored proc like this:</p>
  
  <pre><code>create procedure [dbo].[InsertInput]
  (@ResourceTag int, @Element varchar(50), @PeriodID int, @BackPay varchar(50), @InputValue decimal(18,5), @UserID varchar(50), @Source varchar(50), @InputDate datetime, @SourceID int, @Comments varchar(200), @ProcessID int, @TableCode int, @ScopeID int = NULL Output)
  as
  begin
  
  INSERT INTO 
  [dbo].[Input]
  ([Resource Tag] ,[Element] ,[Period ID] ,[Back Pay], [Input Value], [User ID], [Source], [Input Date], [Source ID], [Comments], [Process ID])
  Values(@ResourceTag, @Element, @PeriodID, @BackPay, @InputValue, @UserID, @Source, @InputDate, @SourceID, @Comments, @ProcessID)
  
  set @ScopeID = (Select Scope_Identity())
  
  end
  </code></pre>
  
  <p>Now I have added the parameter @TableCode, and depending on this value, the above insert statement should go to different tables.
  For example, 
  If @TableCode = 0, then Insert INTO [dbo].[Input]...
  If @TableCode = 1, then Insert INTO [dbo].[Input1]...</p>
  
  <p>So I tried adding a CASE statement like this snippet:</p>
  
  <pre><code>INSERT INTO 
  CASE WHEN @TableCode = 0 THEN
  [dbo].[Input]
  ([Resource Tag] ,[Element] ,[Period ID] ,[Back Pay], [Input Value], [User ID], [Source], [Input Date], [Source ID], [Comments], [Process ID])
  Values(@ResourceTag, @Element, @PeriodID, @BackPay, @InputValue, @UserID, @Source, @InputDate, @SourceID, @Comments, @ProcessID)
  
  set @ScopeID = (Select Scope_Identity())
  </code></pre>
  
  <p>but I just got errors similar to "Incorrect syntax near 'CASE'".</p>
  
  <p>On <a href="https://msdn.microsoft.com/en-us/library/ms181765.aspx" rel="nofollow">this</a> page, I didn't see an example for using CASE with an INSERT, so perhaps it is not possible?</p>
  
  <p>Should I rather use multiple if statements?
  Such as:</p>
  
  <pre><code>IF @TableCode = 0 
  Begin
  INSERT INTO [dbo].[Input]
  ([Resource Tag] ,[Element] ,[Period ID] ,[Back Pay], [Input Value], [User ID], [Source], [Input Date], [Source ID], [Comments], [Process ID])
  Values(@ResourceTag, @Element, @PeriodID, @BackPay, @InputValue, @UserID, @Source, @InputDate, @SourceID, @Comments, @ProcessID)
  
  set @ScopeID = (Select Scope_Identity())
  end 
  
  If @TableCode = 1...
  </code></pre>