Insert into different tables depending on parameter value

Question

I have a stored proc like this:

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

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]...

So I tried adding a CASE statement like this snippet:

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())

but I just got errors similar to "Incorrect syntax near 'CASE'".

On this page, I didn't see an example for using CASE with an INSERT, so perhaps it is not possible?

Should I rather use multiple if statements? Such as:

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...

No you can't. You can't use case statement with insert statement like this. If you wants to use case statement that you have mention then you have to convert your sql statement into dynamic query. — Krishnraj Rana, Aug 25 '15 at 10:27
You should take a look at this article. You are on a slope to a very challenging performance problem if you don't change this up a little bit. http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/ — Sean Lange, Aug 25 '15 at 13:29

score 4 · Accepted Answer · edited May 23 '17 at 10:26

CASE statement returns you a value on the basis of condition and doesn't execute the query, so it cannot be used to control the flow of execution of the SQL Statements. That is why, this error.

If you have just 2 values (or less number of values) for the variable like you have in the example use an if statement.

IF @var=0
INSERT.... table1
ELSE IF @var=1
INSERT.... table2
ELSE
INSERT... table3

otherwise you can use dynamic sql and set table name dynamically. Follow this link:

how to set table name in dynamic sql query?

score 1 · Answer 2 · answered Aug 25 '15 at 10:41

Case is not a flow control element. As mentioned in the CASE msdn page:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

You need to use IF...ELSE to control the flow of the stored procedure.

the fact that it's an insert statement is irrelevant for this issue.

Insert into different tables depending on parameter value

2 Answers2

Related