SQL Server Performance

How to use dynamic table name in stored procedure?

Discussion in 'SQL Server 2005 General Developer Questions' started by enum5, Dec 18, 2006.

  1. enum5 New Member

    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
  2. madhuottapalam New Member

    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 '+@table_name
    exec sp_executesql @SQLStatement



    Madhu
  3. Madhivanan Moderator

    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
  4. enum5 New Member

    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.
  5. Madhivanan Moderator

    <<
    the database has more than hundred similar tables.
    >>

    Why?
    Are you working for a Datawarehouse project?

    Madhivanan

    Failing to plan is Planning to fail
  6. enum5 New Member

    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.

Share This Page