Post reply

Table Name as parameter

  • February 7, 2012 at 3:09 pm

    #252506

    Is it possible to have a dynamic table name? --- Where I can do a select query based on what year the user selects? (This specific project has 1 table per year)

    For example:

    Parameter: @Year

    SELECT dbo.tblTransac@Year.*

    FROM dbo.tblTransac@Year

    So the user picks 2011, I want to pull all records from the tblTransac2011 table.

    Thank you in advance.

  • February 7, 2012 at 3:19 pm

    #1444177

    Not unless you can code using Dynamic-SQL or a series of IF clauses!

  • February 7, 2012 at 3:38 pm

    #1444190

    If you go the dynamic SQL route, read up on SQL Injection first.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 8, 2012 at 7:03 am

    #1444483

    Do you see any problems with this?

    ALTER PROCEDURE [dbo].[SP_MOR_TestingWorking2](

    @fiscalyear char(8)

    )

    AS

    BEGIN

    DECLARE @sql NVARCHAR(100)

    IF ((ISNUMERIC(@fiscalyear) = 1) AND @fiscalyear >=20012002 AND @fiscalyear <=20112012)

    BEGIN

    SET

    @sql = 'SELECT * FROM tblMOR_LU_AdCpGp' + @fiscalyear

    EXEC (@SQL)

    END

    END

  • February 8, 2012 at 7:11 am

    #1444488

    Other than it been an ugly solution?

    Any reason why partitioned views/partitioned tables weren't considered?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 8, 2012 at 7:14 am

    #1444491

    Even with the if statement?

    IF ((ISNUMERIC(@fiscalyear) = 1) AND @fiscalyear >=20012002 AND @fiscalyear <=20112012)

    How would it be possible?

    Thank you 🙂

  • February 8, 2012 at 7:21 am

    #1444500

    ok, your original post changed??

    In this instance I don't have a choice with the table design.

  • February 8, 2012 at 7:32 am

    #1444511

    Personally, I'd rather separate queries, or at most something like this:

    IF (@fiscalyear = '201120011')

    SELECT <column list> FROM tblMOR_LU_AdCpGp201120011

    IF (@fiscalyear = '201020011')

    SELECT <column list> FROM tblMOR_LU_AdCpGp201020011

    ....

    Otherwise you have to account for and handle the errors when somehow something like 20152698 gets passed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

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