SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 table object is not showing.
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/02/2006 :  16:53:58  Show Profile  Visit funketekun's Homepage  Reply with Quote


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
35940 Posts

Posted - 10/02/2006 :  16:57:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why does it need to be dynamic?

Tara Kizer
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/02/2006 :  16:57:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/02/2006 :  16:59:58  Show Profile  Visit funketekun's Homepage  Reply with Quote
i mean does it have to be dynamic sql in order to show the tablename?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/02/2006 :  17:03:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/02/2006 :  18:56:53  Show Profile  Visit funketekun's Homepage  Reply with Quote
tkizer, because im calling many tables i have 500
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/02/2006 :  19:00:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Australia
491 Posts

Posted - 10/02/2006 :  19:18:18  Show Profile  Visit funketekun's Homepage  Reply with Quote
tkizer,
00001
.
.
.
00500
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/02/2006 :  19:20:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Australia
491 Posts

Posted - 10/02/2006 :  19:25:28  Show Profile  Visit funketekun's Homepage  Reply with Quote
TKIZER,
they were here when i came. dinesh designed it.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/02/2006 :  19:29:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Australia
491 Posts

Posted - 10/02/2006 :  19:36:18  Show Profile  Visit funketekun's Homepage  Reply with Quote
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

USA
35940 Posts

Posted - 10/02/2006 :  19:39:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Australia
491 Posts

Posted - 10/02/2006 :  19:54:21  Show Profile  Visit funketekun's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/03/2006 :  00:59:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  08:18:36  Show Profile  Visit funketekun's Homepage  Reply with Quote
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

Australia
491 Posts

Posted - 10/03/2006 :  08:22:08  Show Profile  Visit funketekun's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/03/2006 :  08:25:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/03/2006 :  08:29:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 10/03/2006 08:34:47
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/03/2006 :  08:32:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Australia
491 Posts

Posted - 10/03/2006 :  08:44:36  Show Profile  Visit funketekun's Homepage  Reply with Quote
peso, that is brilliant.
I have to start reading about normalization more.

Edited by - funketekun on 10/03/2006 08:44:51
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000