Results 1 to 2 of 2

Thread: How to use a parameter variable in FROM clause at SQL SERVER

  1. #1
    Loli Guest

    How to use a parameter variable in FROM clause at SQL SERVER


    Hi,

    I'm working with Visual Basic using SQL SERVER to manage my databases.
    I have several tables in my database and I need to do a SELECT from a specific table so I must pass the name of the table as a parameter using a stored procedure:

    CREATE PROCEDURE Obt_Inc_Month(@month char(5))
    AS

    SELECT *
    FROM [@month]
    GO

    When I try to obtain the results fromm Visual Basic:

    ...
    Call Data.Obt_Inc_Month("Jan02&#34

    Set rsMonth = Data.rsObt_Inc_Month
    ...

    There is an error calling the procedure Obt_Inc_Month("Jan02&#34
    Error:
    "Invalid object name @month"

    Please, anybody knows how can I use a parameter variable as a table name to make a SELECT on it?

    Thank you all.
    Loli





  2. #2
    Joe Celko Guest

    How to use a parameter variable in FROM clause at SQL SERVER (reply)

    The short answer is that you hav to use Dynamic SQL. You construct a query as string, then compile that string.

    The right answer is that your whole approach is wrong and you need to learn to think in temrs of SQL and not in BASIC.

    A table models either a relationship or a set of entities in the "real world" that you ar trying to capture. If a procedure can take a table as a parameter, then you are saying that your data model cannot tell the difference among automobiles, marriage licences, octopi, or anything else in the model!! You are trying to do a meta-model, where you treat tables as tables. This is not how a database works.

    Next, a name like 'mon##' implies that the schema is full of too many small tables that are named after the value of an attribute and not the entity to which the attribute belongs. Bad design leads to bad queries.

    What are you actually trying to do?

Posting Permissions

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