Giving Table name as a Variable

Hi guys,

I want to insert record to the table, the table name is a variable. I was tried like this,

declare @.Table_Name as varchar(255)

declare @.MM as varchar(2)

declare @.YY as varchar(4)

set @.MM = month(getdate())

set @.YY = year(getdate())

set @.Table_Name = 'tb_XXXX_' + @.MM + @.YY

INSERT INTO @.Table_Name (column1,....) VALUES (@.Column1,...)

If I print the @.Table_Name variable it shows the corret Table name. But while executing the Procedure it shows a error message

'Must declare the sclar variable @.Table_Name'

How do I implement this? Please anyone had a experience like this tell me a way. Thanks in advance.

Arun.

You have to use the dynamic SQL here. Here the sample to use the dynamic SQL – using sp_executesql or exec

Code Snippet

Create Table #TestDynSQL

(

Id int,

Name varchar(100),

DOB Datetime

);

Declare @.P_Id int, @.P_Name varchar(100), @.P_dob datetime

Declare @.SQL as Nvarchar(4000)

Declare @.ParamDecl as Nvarchar(4000)

Set @.SQL = N'Insert Into #TestDynSQL Values(@.id, @.name, @.dob)'

Set @.ParamDecl = N'@.id int, @.name varchar(100), @.DOB datetime'

Set @.P_id=1

Set @.P_Name='Mani'

Set @.P_Dob='1979-07-26'

--Highly recommanded to use the sp_executesql

Exec sp_executesql @.SQL, @.ParamDecl, @.P_id, @.P_name, @.P_dob

--or

--Beware of SQL injection while using the EXEC(STATEMENT)

Set @.SQL = 'Insert Into #TestDynSQL values(' + cast(@.P_Id as varchar) + ',''' + @.P_Name + ''',''' + Cast(@.P_Dob as varchar) + ''')'

Exec (@.SQL)

Select * from #TestDynSQL

|||

Mani gave you a good solution using dynamic SQL.

However, there are some issues about using dynamic SQL that you should be aware. There may be significant performance penalties and there may also be security issues. The following articles are good resources to learn and explore the use (and mis-use) of dynamic SQL.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx