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
MartinOn 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)|||mrazek@.compik.fd.cvut.cz (Martin Mrazek) wrote in message news:<dcd39e84.0405232210.5a75aeb6@.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 (mrazek@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
