concat command in stored procedure
-
Hi Guys I have a stored procedure which has the coding to insert values in tables with different suffix but one prefix. Eg table_fruits, table_vegetables, table_pulses etc., I am passing the suffix in the procedure. I dont know how to concat the suffix to insert the value in the specific table. create procedure [dbo].[my_proc]( @category varchar(30), @Name varchar(50)) As Begin insert into table_+@category+ value name=@Name; End I dont want to follow if(@category = 'fruits') insert into table_fruits values name=@Name; else if(@category = 'vegetables') etc ., etc., Is there any way to do this. Kindly help me. Thanks Jith
-
Hi Guys I have a stored procedure which has the coding to insert values in tables with different suffix but one prefix. Eg table_fruits, table_vegetables, table_pulses etc., I am passing the suffix in the procedure. I dont know how to concat the suffix to insert the value in the specific table. create procedure [dbo].[my_proc]( @category varchar(30), @Name varchar(50)) As Begin insert into table_+@category+ value name=@Name; End I dont want to follow if(@category = 'fruits') insert into table_fruits values name=@Name; else if(@category = 'vegetables') etc ., etc., Is there any way to do this. Kindly help me. Thanks Jith
-
You will have to build the
INSERTstatement in a varchar variable and execute it dynamically using theEXECUTEcommand.Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
Hi Paul, Thanks for you suggestion. It works well. Thanks Jith
-
You will have to build the
INSERTstatement in a varchar variable and execute it dynamically using theEXECUTEcommand.Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
Hi Mr. Paul I have one more doubt. If I want to get a value using SELECT Command in EXECUTE while using Stored Procedure. How to get it I tried Statement 1 : @GetValue = Exec('Select Id from table_'+@Category+' where name = @Name') Statement 2 : Exec('Select @GetValue = Id from table_'+@Category+' where name = @Name') But both are not working, Thanks Jith
-
Hi Mr. Paul I have one more doubt. If I want to get a value using SELECT Command in EXECUTE while using Stored Procedure. How to get it I tried Statement 1 : @GetValue = Exec('Select Id from table_'+@Category+' where name = @Name') Statement 2 : Exec('Select @GetValue = Id from table_'+@Category+' where name = @Name') But both are not working, Thanks Jith