Using Dynamic Table Name in Select Into

  • I have a stored procedure that accepts one parameter. I want to create a table using static text, the parameter value and the current date. I can create the table name value in a SYSNAME data type but when I try to use the declared variable, @TableName, in the SELECT INTO statement it errors off. What do I need to do to reference the dynamic table name? Partial code below

    ALTER PROCEDURE [dbo].[procPros2ActiveProsCasesBySpecificCounty]

    -- Last three numbers in County FIP, 53 for WA not needed

    @pCountyFIP char(3) = null

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    DECLARE @TableName SYSNAME

    SET @TableName = 'ProsActiveCasesFor53'

    + @pCountyFIP

    + 'For'

    + convert(varchar(2),month(getdate()))

    + '/'

    + convert(varchar(2),day(getdate()))

    + '/'

    + convert(varchar(4),year(getdate()))

    -- Insert statements for procedure here

    SELECT CASE_NO

    INTO @TableName

    FROM SEHC

    WHERE FIP = @pCountyFIP

    END

  • You would need to use dynamic SQL.

    Something like:

    DECLARE @sql VARCHAR(500)

    -- Insert statements for procedure here

    SET @sql=

    'SELECT CASE_NO

    INTO [' +@TableName + ']

    FROM SEHC

    WHERE FIP = ' + @pCountyFIP +'''

    EXEC(@sql)

    Side note: do you really have a table [ProsActiveCasesFor53100For 7/29/2010]??? :pinch:

    I strongly recommend to rethink your table name convention as well as your database design in general if you really have one table per DAY per Count... I'm scared...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Perfect, got what I needed. As far as your concerns these are ad hoc data requests with the user needing the date in the table name. These are temporary tables that are shipped out and deleted. Am in the process of building the front end that will allow the user to access data in real time.

    Once again thanks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply