Author |
Topic |
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-02 : 16:53:58
|
declare @MinVoter varchar(20)declare @field varchar(20)declare @sql1 varchar(100)declare @tableName varchar(20)set @tableName = '00221'print @tableNameprint '======================================='select @field = ad_str1 from @tableName where id_voter = @MinVoterServer: Msg 137, Level 15, State 2, Line 15Must declare the variable '@tableName'.can someone fix this syntax. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-02 : 16:57:41
|
Why does it need to be dynamic?Tara Kizer |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-02 : 16:59:58
|
i mean does it have to be dynamic sql in order to show the tablename? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-02 : 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 |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-02 : 18:56:53
|
tkizer, because im calling many tables i have 500 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-02 : 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
491 Posts |
Posted - 2006-10-02 : 19:18:18
|
tkizer,00001...00500 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-02 : 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
491 Posts |
Posted - 2006-10-02 : 19:25:28
|
TKIZER,they were here when i came. dinesh designed it. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-02 : 19:29:09
|
You should fix it then.Dynamic SQL is bad for performance and security reasons.Tara Kizer |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-02 : 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
38200 Posts |
Posted - 2006-10-02 : 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
491 Posts |
Posted - 2006-10-02 : 19:54:21
|
declare @MinVoter varchar(20)declare @field varchar(20)declare @sql1 varchar(100)declare @tableName varchar(20)set @tableName = '00221'print @tableNameprint '======================================='select @field = ad_str1 from @tableName where id_voter = @MinVoterServer: Msg 137, Level 15, State 2, Line 15Must 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. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-03 : 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 = @MinVoterEXEC (@sqlcmd) Peter LarssonHelsingborg, Sweden |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-03 : 08:18:36
|
quote: Originally posted by tkizer The SELECT portion needs to be dynamic.Tara Kizer
why? |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-03 : 08:22:08
|
peso, declare @MinVoter varchar(20)declare @field varchar(20)declare @sql1 varchar(100)declare @tableName varchar(20)set @tableName = '00221'print @tableNameprint '======================================='[code]set @sql1 = 'select @field = ad_str1 from [' + @tableName + '] where id_voter = @MinVoter'EXEC (@sql1)[code]Server: Msg 170, Level 15, State 1, Line 14Line 14: Incorrect syntax near 'code'.Server: Msg 170, Level 15, State 1, Line 15Line 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 1Must declare the variable '@field'. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-03 : 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 alikeCREATE VIEW vwMyHugeViewASSELECT *, '00000' AS TableName FROM [00000]UNION ALLSELECT *, '00001' AS TableName FROM [00001]UNION ALLSELECT *, '00002' AS TableName FROM [00002]UNION ALLSELECT *, '00003' AS TableName FROM [00003]And when querying, SELECT ad_str1 FROM vwMyHugeView WHERE TableName = @TableName AND id_voter = @MinVoterPeter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-03 : 08:32:16
|
Remove "@field = " from your execution string since @field is not in the same scope.Peter LarssonHelsingborg, Sweden |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2006-10-03 : 08:44:36
|
peso, that is brilliant.I have to start reading about normalization more. |
|
|
Next Page
|