| Author |
 Topic  |
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
 Posted - 10/02/2006 : 16:53:58
|
declare @MinVoter varchar(20)
declare @field varchar(20)
declare @sql1 varchar(100)
declare @tableName varchar(20)
set @tableName = '00221'
print @tableName
print '======================================='
select @field = ad_str1
from @tableName
where id_voter = @MinVoter
Server: Msg 137, Level 15, State 2, Line 15
Must declare the variable '@tableName'.
can someone fix this syntax. |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/02/2006 : 16:57:41
|
Why does it need to be dynamic?
Tara Kizer |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/02/2006 : 16:57:54
|
quote:
Originally posted by funketekun
can someone fix this syntax.
Yes. Prepare yourself by reading http://www.sommarskog.se/dynamic_sql.html
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/03/2006 08:22:13 |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/02/2006 : 16:59:58
|
| i mean does it have to be dynamic sql in order to show the tablename? |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/02/2006 : 17:03:01
|
The SELECT portion needs to be dynamic.
But your query indicates bad database design. Why does the table name need to be dynamic?
Tara Kizer |
Edited by - tkizer on 10/02/2006 17:03:43 |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/02/2006 : 18:56:53
|
| tkizer, because im calling many tables i have 500 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/02/2006 : 19:00:57
|
That is not a reasonable answer for this. What do your table names look like?
We've got hundreds of tables and have not had to use dynamic SQL.
Tara Kizer |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/02/2006 : 19:18:18
|
tkizer,
00001
.
.
.
00500 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/02/2006 : 19:20:42
|
And why do you have tables like this? Who designed this database?
What I am trying to get at is that this is not good database design. You aren't properly normalized.
Tara Kizer |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/02/2006 : 19:25:28
|
TKIZER,
they were here when i came. dinesh designed it. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/02/2006 : 19:29:09
|
You should fix it then.
Dynamic SQL is bad for performance and security reasons.
Tara Kizer |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/02/2006 : 19:36:18
|
how can i call this 500 tables if i dont use dinamyc sql?
lets say i call 00001 and do something with it and stored it in a temp_00001. i wanna do this to 500 tables.
my first question. why doesnt @tablename changed it to 00221? |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/02/2006 : 19:39:14
|
You are missing the point. If you changed your database design, you wouldn't need to change the table name dynamically, therefore you wouldn't need dynamic SQL.
quote:
my first question. why doesnt @tablename changed it to 00221?
I don't understand the question.
Tara Kizer |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/02/2006 : 19:54:21
|
declare @MinVoter varchar(20)
declare @field varchar(20)
declare @sql1 varchar(100)
declare @tableName varchar(20)
set @tableName = '00221'
print @tableName
print '======================================='
select @field = ad_str1
from @tableName
where id_voter = @MinVoter
Server: Msg 137, Level 15, State 2, Line 15
Must declare the variable '@tableName'.
why doesnt @tablename changed it to 00221?
what type of desgin should i use?
p.s. we 'must' go through every table individually.
|
Edited by - funketekun on 10/02/2006 19:55:56 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/03/2006 : 00:59:54
|
Table names can't for some reason start with a number. Put brackets [] around the table name and that should do it.
But listen very carefully to Kristen!
If you are only interested in selecting from the 500 tables, create a view that UNIONs all the tables.
set @sqlcmd = 'select @field = ad_str1 from [' + @tableName + '] where id_voter = @MinVoter
EXEC (@sqlcmd)
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/03/2006 08:20:16 |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/03/2006 : 08:18:36
|
quote:
Originally posted by tkizer
The SELECT portion needs to be dynamic.
Tara Kizer
why? |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/03/2006 : 08:22:08
|
peso,
declare @MinVoter varchar(20)
declare @field varchar(20)
declare @sql1 varchar(100)
declare @tableName varchar(20)
set @tableName = '00221'
print @tableName
print '======================================='
[code]set @sql1 = 'select @field = ad_str1 from [' + @tableName + '] where id_voter = @MinVoter'
EXEC (@sql1)[code]
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'code'.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near 'code'.
I also tried without '[code]'
it shows me this error msg:
00221
=======================================
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@field'.
|
Edited by - funketekun on 10/03/2006 08:23:15 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/03/2006 : 08:25:37
|
The code tags are only for displaying the code here on SQLTeam. Remove both [code] and you're set.
quote:
Originally posted by funketekun
how can i call this 500 tables if i dont use dinamyc sql?
A workaround can be by creating a VIEW. If all tables are alike
CREATE VIEW vwMyHugeView
AS
SELECT *, '00000' AS TableName FROM [00000]
UNION ALL
SELECT *, '00001' AS TableName FROM [00001]
UNION ALL
SELECT *, '00002' AS TableName FROM [00002]
UNION ALL
SELECT *, '00003' AS TableName FROM [00003]
And when querying, SELECT ad_str1 FROM vwMyHugeView WHERE TableName = @TableName AND id_voter = @MinVoter
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/03/2006 08:33:54 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/03/2006 : 08:32:16
|
Remove "@field = " from your execution string since @field is not in the same scope.
Peter Larsson
Helsingborg, Sweden |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/03/2006 : 08:44:36
|
peso, that is brilliant.
I have to start reading about normalization more. |
Edited by - funketekun on 10/03/2006 08:44:51 |
|
|
|
Topic |
|
table object is not showing.
