0

For example:

exec SP_ChangeNameOfTable tbl1_data_20200124


SELECT * 
INTO #TEMP_DATA 
FROM [LOADED].DBO.tbl1_data_20200123 
WHERE [Owner] IS NULL

I want to change this table name from 20200123 to 20200124 using a stored procedure.

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Regan
  • 21
  • 2
  • Dynamic SQL? https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15 – Максим Золотенко Jan 24 '20 at 07:23
  • Does this answer your question? [Table name as variable](https://stackoverflow.com/questions/2838490/table-name-as-variable) – Максим Золотенко Jan 24 '20 at 07:24
  • This would NOT be a problem if you did not embed important facts in the name of the table. This path leads to madness. Go back before it's too late. And tsql already has a function to [rename a table](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver15). – SMor Jan 24 '20 at 12:51

1 Answers1

0

you need to use dynamic sql while trying to pass dynamic table name in SP.

something like,

create procedure SP_ChangeNameOfTable
(
  @table_name as varchar(200)
)
As
begin
declare @sc as varchar(max)='SELECT * into #TEMP_DATA FROM
[LOADED].[dbo].'+@table_name+' where [Owner] is null'
execute(@sc);
end

Call the SP as you like,

exec SP_ChangeNameOfTable 'tbl1_data_20200124'

Demo

A_Sk
  • 4,200
  • 3
  • 24
  • 47