Select Table Name using variable

  • I have a large table in a database like the following:

    glpl300905

    glpl011005

    glpl021005

    etc

    The tables are created on a daily basis. glpl021005 represents data of 2nd oct. 2005. however the table does not contain the date for which it pertains to.

    Now i want to write a query wherby by declaring the date i must select the correct table

    Help me out

  • Hi,

       Try to compose the sql statement in a variable and execute the sql statement using execute command.

    Example:

    declare lSqlStatement varchar(2000)

    set lSqlStatement = 'Select * from ' + your date variable + 'sometext whatever you want'

    execute (lSqlStatement)

     

    Regards,

    Saravanan.


    Kindest Regards,

    Saravanan V
    geocities.com

  • Dear Mr. Saravanan,

    Thank u very much for ur immediate response. I tried your suggestion. The script is as below:

    declare @lSqlStatement varchar(2000)

    declare @sdate varchar(10)

    set @sdate = '300905'

    set @lSqlStatement = 'Select * from glpl' + @sdate + ' where brcode=102 '

    execute (@lSqlStatement)

    I get an error ' Invalid object name 'glpl300905'.

    Server: Msg 208, Level 16, State 1, Line 1'

    Kindly help me more

  • Hi,

       Try to put the table name with dbname.ownername

    For example:

    set @lSqlStatement = 'Select * from mydb.dbo.glpl' + @sdate + ' where brcode=102 '

    Try like this. i hope that, this will work.

    Regards,

    Saravanan.


    Kindest Regards,

    Saravanan V
    geocities.com

  • Thank u Mr. Saravanan

    It works fine

Viewing 5 posts - 1 through 4 (of 4 total)

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