Professional Web Applications Themes

How to use a variable in my SQL code? - Microsoft SQL / MS SQL Server

Hi! I want to be able to do the following in my stored procedure: declare v_tablename varchar(30) set v_tablename = 'dbo.[MyTable]' select * form v_tablename Is it possible to do this inside SQL Query yzer? Thanks for your input. Greg...

  1. #1

    Default How to use a variable in my SQL code?

    Hi!

    I want to be able to do the following in my stored procedure:

    declare v_tablename varchar(30)
    set v_tablename = 'dbo.[MyTable]'
    select * form v_tablename

    Is it possible to do this inside SQL Query yzer?

    Thanks for your input.

    Greg


    Greg_Del_Pilar Guest

  2. #2

    Default Re: How to use a variable in my SQL code?

    You'll need to use dynamic SQL to do this sort of thing. Check out
    Erland's article on dynamic SQL considerations:

    http://www.algonet.se/~sommar/dynamic_sql.html

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Greg_Del_Pilar" <com> wrote in message
    news:phx.gbl... 


    Dan Guest

  3. #3

    Default Re: How to use a variable in my SQL code?

    The short answer is use slow, proprietrary dynamic SQL to kludge a query
    together on the fly with your table name in the FROM clause.

    The right answer is never pass a table name as a parameter. You need to
    understand the basic idea of a data model and what a table means in
    implementing a data model.

    1) This is dangerous because some user can insert pretty much whatever
    they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
    FROM Floob' in your statement string.

    2) It says that you have no idea what you are doing, so you are giving
    control of the application to any user, present or future. Remember the
    basics of Software Engineering? Modules need weak coupling and strong
    cohesion, etc.

    3) If you have tables with the same structure which represent the same
    kind of entities, then your schema is not orthogonal. Look up what
    Chris Date has to say about this design flaw.

    Go back to basics. What is a table? A model of a set of entities or
    relationships. EACH TABLE SHOULD BE A DIFFERENT KIND OF ENTITY.

    What a generic procedure that works equally on automobiles, octopi or
    Britney Spear's discology is saying that your applications a disaster of
    design because you have:

    1) failed to put all items of the same kind into one table. Chris date
    calls this orthogonal design, and I call it attribute splitting. Common
    example, 12 identical tables, one for each month, with the same
    information them instead of a single table with a temporal attribute.

    2) failed to tell the difference between data and meta-data. The SQL
    engine has routines for that stuff and applications do not work at that
    level, if you want to have any data integrity.

    Yes, you can write a program with dynamic SQL to kludge something like
    this. it will last about a year in production and then your data
    integrity is shot.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: How to use a variable in my SQL code?

    "Greg_Del_Pilar" <com> wrote in
    news:phx.gbl:
     

    You could use EXEC to do this:

    DECLARE cmd varchar(100)
    SET cmd = 'select * from ' + v_tablename
    EXEC (cmd)

    Depending on why you are doing this, I would be careful.
    Mark Guest

  5. #5

    Default Re: How to use a variable in my SQL code?

    Another option you might consider is to have separate databases for
    development and production. If this is a full-n development
    environment, the databases would ideally be on separate servers but you
    can get away with different (named) instances on the same server or even
    different database names on the same instance.

    We usually have separate servers for development, QA, UAT and
    production. Server and database names are configurable within our
    applications and we keep all SQL scripts under source control. This
    allows us to reliably promote schema changes as part of our
    configuration management process and avoid nasty dynamic SQL as well.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Greg_Del_Pilar" <com> wrote in message
    news:%phx.gbl... 
    not 
    SQL 
    and 
    thought 
    for the 
    (which is 
    >
    >[/ref]


    Dan Guest

Similar Threads

  1. Parsing CF Code Inside A Variable
    By RoBsTaMaCk in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 26th, 03:37 PM
  2. How do I pass a variable into a PHP piece of code
    By Bob in forum PHP Development
    Replies: 1
    Last Post: May 1st, 02:22 PM
  3. my code erronousely changes the variable
    By george in forum PHP Development
    Replies: 1
    Last Post: September 9th, 09:38 AM
  4. Content of Variable ist html code
    By Matthias Wulkow in forum PHP Development
    Replies: 1
    Last Post: August 24th, 04:25 PM
  5. Using like on :new variable in trigger code
    By Todd Boss in forum Oracle Server
    Replies: 2
    Last Post: December 19th, 09:51 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139