SQL Server Performance Forum – Threads Archive

How to use dynamic table name in stored procedure?

I am using dynamic table name in a stored procedure to select records from table. I try with the code below, but error occurs. Is there a way to do this?
CREATE PROCEDURE [DBO].[select_audit_record] @year INT, @month INT AS
DECLARE @table_name VARCHAR(250) SET @table_name = ‘AuditLog_’ + cast(@year AS VARCHAR(4)) + cast(@month AS VARCHAR(2))
SELECT * FROM @table_name
GO

---

hi, You must use dynamic sql for this; DECLARE @table_name NvarCHAR(250),@SQLStatement nvarchar(200)
SET @table_name = ‘AuditLog_’ + cast(@year AS VARCHAR(4)) + cast(@month AS VARCHAR(2))
set @SQLStatement= ‘SELECT * FROM ‘[email protected]_name
exec sp_executesql @SQLStatement Madhu

---

1 You should avoid passing object names as parameters
2 or read this fully
www.sommarskog.se/dynamic_sql.html Madhivanan Failing to plan is Planning to fail

---

Thank you, madhuottapalam, sp_executesql solve my problem.
Thanks to Madhivanan too, the article is interesting. The stored procedure I created works well after using sp_executesql. I decide to use dynamic sql to solve the problem because the database has more than hundred similar tables.

---

<<
the database has more than hundred similar tables.
>> Why?
Are you working for a Datawarehouse project? Madhivanan Failing to plan is Planning to fail

---

It is not a Datawarehouse project. The database belongs to an application in a server. There are 1-2 million new records everyday. The application stores the records into database using monthly table.

---

]]>