Hi,
I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
from year 1997, 1998 and a027 is just specification of product type. I
need carry out repeated operataions over these tables. To generate
their names is easy, for example
use [P5-01]
declare @Yint int, @Y char(2), @tabName char(8)
set @Yint = 1997
while @Yint < 2002
begin
set @tabName = 'ag' + substring(conve rt(char(4),@Yin t),3,2)+'a027'
print @tabName
set @Yint = @Yint + 1
/*
select *
from @tabName
*/
end
prints properly
ag97a027
ag98a027
ag99a027
ag00a027
ag01a027
but when I uncomment the select statement, MS-SQL server responds:
Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@tabName'.
How to use @local_variable for browsing through tables in above
described way?
thanks
Martin 3 2175
On 23 May 2004 23:10:43 -0700, Martin Mrazek wrote: Hi, I have tables named like ag97a027, ag98a027, ... where 97 and 98 is from year 1997, 1998 and a027 is just specification of product type. I need carry out repeated operataions over these tables. To generate their names is easy, for example
use [P5-01] declare @Yint int, @Y char(2), @tabName char(8) set @Yint = 1997 while @Yint < 2002 begin set @tabName = 'ag' + substring(conve rt(char(4),@Yin t),3,2)+'a027' print @tabName set @Yint = @Yint + 1 /* select * from @tabName */ end
prints properly
ag97a027 ag98a027 ag99a027 ag00a027 ag01a027
but when I uncomment the select statement, MS-SQL server responds: Server: Msg 137, Level 15, State 2, Line 11 Must declare the variable '@tabName'.
How to use @local_variable for browsing through tables in above described way?
thanks Martin
Hi Martin,
The error message is caused because SQL Server accepts either a literal
table name or a table variable in the from clause. The @ denotes the next
thing as a variable, so SQL Server will try to find a table variable
called @tabName, which it can't find.
The best answer to your question is: change your table design. Generally
speaking, 99 out of 100 cases where each year's data is stored in a
seperate table are better off storing everything in one table (adding a
"year" column if there isn't already a "date" column that can serve this
purpose), or using a two table design (one "current" table and one
"history" table), again adding a "year" column if needed.
If you feel you must do it this way, google this newsgroup for "dynamic
SQL". That's the kludge that will do what you're trying to achieve.
(I assume that the above SQL is used only to illustrate your problem and
that you're not actually using "select *" in production code or printing
all data in all tables - who would ever want to read that much output??).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address) mr****@compik.f d.cvut.cz (Martin Mrazek) wrote in message news:<dc******* *************** ****@posting.go ogle.com>... Hi, I have tables named like ag97a027, ag98a027, ... where 97 and 98 is from year 1997, 1998 and a027 is just specification of product type. I need carry out repeated operataions over these tables. To generate their names is easy, for example
use [P5-01] declare @Yint int, @Y char(2), @tabName char(8) set @Yint = 1997 while @Yint < 2002 begin set @tabName = 'ag' + substring(conve rt(char(4),@Yin t),3,2)+'a027' print @tabName set @Yint = @Yint + 1 /* select * from @tabName */ end
prints properly
ag97a027 ag98a027 ag99a027 ag00a027 ag01a027
but when I uncomment the select statement, MS-SQL server responds: Server: Msg 137, Level 15, State 2, Line 11 Must declare the variable '@tabName'.
How to use @local_variable for browsing through tables in above described way?
thanks Martin
You can use dynamic SQL for this (see below), but read this first to
understand why you probably shouldn't: http://www.sommarskog.se/dynamic_sql.html#Sales_yymm
Simon
declare @tblname sysname
set @tblname = 'MyTable'
exec('select * from ' + @tblname)
Martin Mrazek (mr****@compik. fd.cvut.cz) writes: How to use @local_variable for browsing through tables in above described way?
The answer is you don't. Well, you can if you go over hops of dynamic
SQL, but you should really only have one table, with year and product
types as keys in your one single table.
If you have some application which really requires all these tables,
then set up a view over all the tables, with year and product type
added:
CREATE VIEW all_my_tables (year, product_type, col1, col2, col3, ...) AS
SELECT '1998', 'a027', col1, col2, col3,
FROM ag98a027
UNION ALL
SELECT '1999', 'a027', col1, col2, col3,
FROM ag99a027
UNION ALL
...
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Sangbae |
last post by:
I cannot create table using adodbapi.
I have no problem with SELECT etc.
But I can create table using win32com.client directly.
I don't know why.
Could you give some advice, please?
This is my source.
=====================================
|
by: vbnetrookie |
last post by:
I have a table name in SQL Server 2000 that has a space in it
ex: aim international
I had trouble just in the query analyzer with this..I had to place the
name in brackets for it to work. But now I'm in Visual Studio .Net
2003 and it gives me another problem. I get the table name from a drop
down list selection and send it to a query...
|
by: dixie |
last post by:
I want to write a query where one of the two tables in it is variable and
its name is picked up from a text box control on an open form. The query is
very simple and is only a select query with three fields, but the problem is
that one of its tables varies with the Calendar Year like tblYear2002,
tblYear2003, etc.
Is there a simple way of...
|
by: young |
last post by:
Hello all,
I would like to use same SQL statement in my SQC program for several
tables
I tried something like that:
- sql_sts="SELECT col1, col2 FROM ?"
- EXEC SQL PREPARE dsql FROM :sql_sts;
- EXEC SQL DECLARE cTab CURSOR WITH HOLD FOR dsql;
- EXEC SQL OPEN cTab USING :tabName;
|
by: Andre.Iskandar |
last post by:
Hi,
I make an acess program using Access 2000 and convert it to Access
2002. When I convert it back to Access 2000, I cannot see table, query
and form names (maybe other tabs too). But they exist (I try to access
via menu). I try Tools:Option:Show Hidden Objects, but brings no
result.
How can I see the names again in access 2000 without...
| |
by: laurentc |
last post by:
Dear all,
I have several tables based on exactly the same fields
(Key/Date/Price1/Price2).
I do some statistics on the prices.
However, as I have many different tables (the tables are different
because I directly import the data from a .csv file), I do not want to
create dozens of queries to be able to get the results of the different...
|
by: s.amann |
last post by:
Hello,
I get the following error when I try to insert data into a table.
"SQL1477N Table "<table-name>" cannot be accessed. "
The table was created with NOT LOGGED INITIALLY
The import is done by a program written in C and using embedded SQL.
It starts with ALTER TABLE myTable ACTIVATE NOT LOGGED INITIALLY WITH
EMPTY TABLE.
Then I...
|
by: Vivek |
last post by:
Hi,
Generally we work with the 2 part qualified table names in db2 i.e.
<schema>. <table name>
Is there a more fully qualified table name ? I've seen that a select
from <database name>.<schema>.<tabnamealso works.
This brings me to the next question. Is it possible to refer to a
table in database 2 when connected to database 1 ? If so...
|
by: Ronald S. Cook |
last post by:
I have been given XML similar to the below. In this example, Bob manages
Sue, Dan manages Ron, and Mac manages Amy.
I tried importing the XML into a DataSet using this code:
XmlDataDocument WebsiteXmlDataDocument = new XmlDataDocument();
WebsiteXmlDataDocument.DataSet.ReadXml(new
StreamReader(Request.PhysicalApplicationPath +...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |