Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Dynamic sql statement

    I would like to know if it is possible to pass a table name to the from section of a sql select statement?
    Something like:
    Declare @paramTable as nvarchar(10)
    Set @paramTable = TableName
    Select firstname, surname from @paramTable

    Is this possible?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Only using dynamic SQL, where you SELECT statement is constructed as a string and then executed.
    This is not advisable...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    or sp_execute_sql
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Ok. What about in a stored procedure then.

    Say I have multiple tables each one contacining data from each month of the year. If I pass through January it must use the January table, February the February table and so on.
    Any idea how this can be done?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    create a partitioned view then...are you creating tables on the fly?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A table named "January"? I am afraid to ask what you do at the start of a new year.
    You need to rethink your design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Please I am not that dumb blindman, give me a little credit. Purely was an example and a very rushed one at that.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good one, jez

    from that article:
    Now, let's make this very clear: this is a flawed table design. You should not have one sales table per month, you should have one single sales table, and the month that appear in the table name, should be the first column of the primary key in the united sales table. At least logically. Sometimes, when you have huge tables (say over 10 GB in size), partitioning can be a good idea, but you should do it right and use partitioned views, that we will look at in this section. I like to make the point that since SQL Server is a enterprise RDBMS, it can handle very large tables very efficiently, as long as you keep in mind that good indexing is essential. A few million rows is no cause for concern.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    i would edit that quote and replace "partitioned views" with "table using a partition function". less to manage that way. only exists on 2005 though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •