adding a table as parameter in a query string in a stored procedure

Question

I have the following query :

   ALTER procedure [dbo].[jk_insertAllLocation]
   @locationTbl as locationTable readonly,
   @TableName varchar(100)
   as
   declare @tbl as locationTable,@sql varchar(max)
   begin
        set @sql = 'insert into ' + @TableName +'(location_id,name,address,latitude,longitude,distance,state,sub_cat,id_cat,icon_link,checkinsCount,IDSearch)
        select * from ' +  @locationTbl
        exec sp_executesql @sql
   end

I need to pass a table and a table name as parameter and I need to insert in the table name (@TableName) passed as parameter all the data in the table (@locationTbl) passed as parameter

but I know that I cannot concatenate the table (@locationTbl) in the query ...

so how can I fix this?

Answer 1

You can use temp tables (Temporary tables section on link):

ALTER procedure [dbo].[jk_insertAllLocation]
  @locationTbl as locationTable readonly,
  @TableName varchar(100)
as
begin
  declare @tbl as locationTable,@sql varchar(max)
  if object_id('#_tmp_location_table') is not null drop table #_tmp_location_table
  select * into #_tmp_location_table from @locationTbl
  set @sql = 'insert into ' + @TableName + '(location_id,name,address,latitude,longitude,distance,state,sub_cat,id_cat,icon_link,checkinsCount,IDSearch) select * from #_tmp_location_table'
  exec sp_executesql @sql
end