Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 table object is not showing.

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 @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

38200 Posts

Posted - 2006-10-02 : 16:57:41
Why does it need to be dynamic?

Tara Kizer
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-02 : 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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-10-02 : 18:56:53
tkizer, because im calling many tables i have 500
Go to Top of Page

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
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-10-02 : 19:18:18
tkizer,
00001
.
.
.
00500
Go to Top of Page

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
Go to Top of Page

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-10-02 : 19:25:28
TKIZER,
they were here when i came. dinesh designed it.
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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 @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.
Go to Top of Page

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 = @MinVoter
EXEC (@sqlcmd)

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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?
Go to Top of Page

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 @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'.
Go to Top of Page

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 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 08:29:59
I have a feeling that funketekun and Gonxia649 are the same person.

Same type of queries here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71598&whichpage=2 and here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72201

id_voter and ad_str1 are same column names. And the same desire to use dynamic SQL.
Perhaps I am wrong and they use the same text book, or having the same teacher.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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.
Go to Top of Page
    Next Page

- Advertisement -