sql server - Insert into different tables depending on parameter value -


i have stored proc 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) begin  insert  [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 have added parameter @tablecode, , depending on value, above insert statement should go different tables. example, if @tablecode = 0, insert [dbo].[input]... if @tablecode = 1, insert [dbo].[input1]...

so tried adding case statement snippet:

insert  case when @tablecode = 0 [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 got errors similar "incorrect syntax near 'case'".

on this page, didn't see example using case insert, perhaps not possible?

should rather use multiple if statements? such as:

if @tablecode = 0  begin insert [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... 

case statement returns value on basis of condition , doesn't execute query, cannot used control flow of execution of sql statements. why, error.

if have 2 values (or less number of values) variable have in example use if statement.

if @var=0 insert.... table1 else if @var=1 insert.... table2 else insert... table3 

otherwise can use dynamic sql , set table name dynamically. follow link:

how set table name in dynamic sql query?


Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -