473,320 Members | 1,990 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

slecting a table using a local variable name

I am new at sql so would appreciate some help
I have the name of a table in alocal variable is it possible to select this
table
DECLARE @name sysname

SET @name = 'tblSniffedItems'

PRINT @name

SELECT * FROM @name

I expected this wo work but I got the following error.
@name is declare as far as I know

Server message

Must declare the variable '@name'.

Thanks in advance

Andre
Jul 23 '05 #1
4 6019
Yes it is, although not quite in the way you are attempting.

Here is an article by Erland Sommarskog covering precisely this topic.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dynamic_sql.html#Dyn_table (select * from
@tablename)

Mr Tea

"Andre Arpin" <ar***@kingston.net> wrote in message
news:Vc********************@wtccommunications.ca.. .
I am new at sql so would appreciate some help
I have the name of a table in alocal variable is it possible to select
this table
DECLARE @name sysname

SET @name = 'tblSniffedItems'

PRINT @name

SELECT * FROM @name

I expected this wo work but I got the following error.
@name is declare as far as I know

Server message

Must declare the variable '@name'.

Thanks in advance

Andre

Jul 23 '05 #2
Andre Arpin (ar***@kingston.net) writes:
I am new at sql so would appreciate some help I have the name of a table
in alocal variable is it possible to select this table
DECLARE @name sysname

SET @name = 'tblSniffedItems'

PRINT @name

SELECT * FROM @name

I expected this wo work but I got the following error.
@name is declare as far as I know

Server message

Must declare the variable '@name'.


The error message is somewhat misleading. @name is indeed declared,
but the declaration is not appropriate for the context. This would
be:

DECLARE @tbl TABLE (a int NOT NULL)

INSERT @tbl (a) VALUES (12)

SELECT * FROM @tbl

That is. @tbl is a table variable. A table variable is similar to a
temp table, those of which the name starts with #. There are some
subtle differences between them, but I will not go into that now.

As for doing what you trying to do, the answer is that this is something
you normally don't do. A table is very much a singular item, which in
the general case is different from all tables. And even if two tables
are structurally similar, that is have the same columns etc, they are
are still two individual items as far as the optimizer is concerned.

SELECT * FROM a WHERE col = 1
SELECT * FROM b WHERE col = 1

may yield two different query plans, because the data distribution is
different. For one table using a non-clustered index on col is a good
idea, but be a bad idea for the other.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
Do not write SQL like this. It violates basic software engineering
principles about cohesion in modules. That is more fundamental than
just SQL.

Jul 23 '05 #4
How about a utility module that takes a given table and a filter clause as
text string, then procedes to exececute the filter clause against that table
and generates the set of INSERT scripts to re-create the data matching the
filter clause.

This would be a legitimate(and extremely useful in the context of this
forum) use for such a piece of SQL, wouldnt you agree?

Mr Tea

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Do not write SQL like this. It violates basic software engineering
principles about cohesion in modules. That is more fundamental than
just SQL.

Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: dzieciou | last post by:
I've used JENA and got the following result in result of query to RDF file: <j.0:ResultSet> <j.0:solution rdf:parseType="Resource"> <j.0:binding rdf:parseType="Resource"> <j.0:value>John...
3
by: Sandros | last post by:
Background: I'm collecting usability statistics for a group of applications. Each count has the following attributes: date, application, major heading, minor heading, count. My intent is to pull...
1
by: luvdairish | last post by:
Can someone please look at my code and see why tables are not hiding properly? <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD><TITLE>Untitled Document</TITLE> <META...
4
by: afc46220 | last post by:
Hello, I am a novice with XSL, and can't quite figure out how to come up with a solution for the following problem. Let's assume we have the following set of XML nodes: <DataBlocks>...
2
by: vvyshak | last post by:
Hi all... I have a table in which some columns has distinct values and some has duplicates..i wan to select all the columns with distinct values....no problem if rows has null value in it....i...
13
by: Andy Baxter | last post by:
Can anyone recommend a good online guide to using objects in javascript? The book I bought (DHTML Utopia) suggests using objects to keep the code clean and stop namespace clashes between different...
2
by: rn5a | last post by:
Using the FileSystemInfo class, I am retrieving all the directories & files existing in a particular directory on the server & listing them in a ListBox. If an item in the ListBox happens to be a...
3
by: dmorand | last post by:
I'm very new to stored procedures so this is probably something very dumb. I want to pass a name of a table to be created to my stored procedure. I'm using the variable @tableName in the code below...
6
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.