Professional Web Applications Themes

Table name in variables? - Microsoft SQL / MS SQL Server

Hello, everybody. I have written the following stored procedure: create procedure Testing tabname varchar(50) AS select * from tabname and found that it doesn't work. I want to know if there is any way for me to do the same thing as the above procedure does. May be this is a silly question and sorry for that as I am a very beginner in SQL Server 2000...

  1. #1

    Default Table name in variables?

    Hello, everybody.
    I have written the following stored procedure:

    create procedure Testing
    tabname varchar(50)
    AS
    select * from tabname

    and found that it doesn't work. I want to know
    if there is any way for me to do the same thing
    as the above procedure does. May be this is a
    silly question and sorry for that as I am a very
    beginner in SQL Server 2000
    Lovely Guest

  2. #2

    Default Re: Table name in variables?

    Hi,
    You can't do what you want like that but you can do it like this

    create procedure Testing
    tabname varchar(50)
    AS
    exec('select * from ' + tabname)

    There are a few issues though. There is no real benifit to doing this you
    might as well just issue the sql statement as the optimiser will have to do
    work each time this is called.
    It is more typical to have a SP per function not one SP to cover all tables.


    --
    I hope this helps
    regards
    Greg O MCSD
    SQL Scribe Doentation Builder
    Doent any SQL server database in minutes
    Programmers love it, DBA dream of it
    AGS SQL Scribe download a 30 day trial today
    http://www.ag-software.com/ags_scribe_index.asp


    "Lovely Dola" <com> wrote in message
    news:google.com... 


    Greg Guest

  3. #3

    Default Re: Table name in variables?

    To add to Greg's response, it makes more sense (and easier) to build SQL
    statements on the client side. Also, 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
    -----------------------

    "Lovely Dola" <com> wrote in message
    news:google.com... 


    Dan Guest

  4. #4

    Default Re: Table name in variables?

    The short answer is that you can use dynamic SQL -- it is very slow,
    proprietary and dangerous.

    The real answer is that you do not write code in which you don't know if
    you are working with a table that represents automobiles, marriages,
    octopi or anything else in the universe. You should have control over
    the database and not give unlimited power to an unknown future user.

    --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

Similar Threads

  1. Replies: 14
    Last Post: December 19th, 11:58 AM
  2. Replies: 1
    Last Post: November 11th, 10:35 PM
  3. merge two table's data with variables
    By rupert in forum MySQL
    Replies: 1
    Last Post: October 3rd, 06:42 AM
  4. buttons and variables in a table cell
    By Gary MacKay in forum PHP Development
    Replies: 0
    Last Post: August 28th, 05:05 PM
  5. Replies: 2
    Last Post: August 12th, 07:55 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