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(convert(char(4),@Yint),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 2172
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(convert(char(4),@Yint),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.fd.cvut.cz (Martin Mrazek) wrote in message news:<dc**************************@posting.google. 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(convert(char(4),@Yint),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...
|
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...
|
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...
|
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...
|
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...
| |
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...
|
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...
|
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...
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |