getting table name from another table...

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

SQL Server 2000 Forums

SQL Server Development (2000)

getting table name from another table...

Author

Topic

e106199
Starting Member

13 Posts

Posted - 2005-12-13 : 09:52:13

hi all,
i have a table that keeps some table names. Depending on the given ID i go to this table get the table name and want to run a select statement over this table. Here is what i did so far(this is running in a stored procedure):
.......
DECLARE @TABLENAME VARCHAR(50)
set @TABLENAME = (SELECT TABLENAME FROM typeTable WHERE TYPEID = @p1) --now the @tablename is the name of the table i want

select myCODE from anotherTable
where ID = (SELECT ID2 FROM @TABLENAME WHERE ID = @p1)
.........
here i m getting a "Must declare variable @TABLENAME". Its declared i think so i dont know what am i doing wrong here.
Any ideas?
thanks in advance
-shane

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 10:38:26

You would have to use dynamic SQL for the second bit - so something like:


DECLARE @strSQL varchar(8000)
SELECT @strSQL = 'select myCODE from anotherTable 
where ID = (SELECT ID2 FROM ' + @TABLENAME + ' WHERE ID = ' 
    + CONVERT(varchar(20), @p1) + ')'
EXEC (@strSQL)

Kristen

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-13 : 10:40:37

Do it as follows:

DECLARE @TABLENAME VARCHAR(50)
set @TABLENAME = (SELECT TABLENAME FROM typeTable WHERE TYPEID = @p1)

Declare sSQL Varchar(1000)

Set sSQL = 'select myCODE from anotherTable where ID = (SELECT ID2 FROM ' + @TABLENAME + ' WHERE ID = ''' + @p1 + ''''

Exec (@sSQL)

U can put a Select or print statement in each place and c whether every statement is correct (I mean returning ur desired result).

I am a bit late

e106199
Starting Member

13 Posts

Posted - 2005-12-13 : 10:45:13

THANK YOU GUYS,
THIS IS WHAT I DID AND IT WORKED AS EXPECTED.
-SHANE

e106199
Starting Member

13 Posts

Posted - 2005-12-13 : 11:55:44

hi,
it worked fine but i have a new problem now.
i have 2 parameters, and one is an output parameter.
i tried changin the statement to:

exec ('select' +@p2+ '= myCODE from anotherTable
where ID = (SELECT ID2 FROM [' + @TABLENAME + ]' WHERE ID = ' + @p1 +') ' )

but it wont work. it says there is an incorrect syntax near '='
what am i doing wrong here?

@p2 is my output parameter (@p2 varchar(50) output)
thank you
-shane

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 13:57:24

You'll need to use sp_ExecuteSQL instead of EXEC. If you are going down that route you should parameterise EVERYTHING you can - for performance reasons - in this case @p1 as well as your OUTPUT parameter @p2


DECLARE @strSQL varchar(8000)
SELECT @strSQL = 'select @p2 = myCODE from anotherTable 
where ID = (SELECT ID2 FROM ' + @TABLENAME + ' WHERE ID = @p1)'
EXEC sp_ExecuteSQL @strSQL, N'@p1 int, @p2 varchar(50) output', @p1, @p2 OUTPUT

Kristen

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-13 : 14:37:48

U can do the following

Set sSQL = 'select myCODE from anotherTable where ID = (SELECT ID2 FROM ' + @TABLENAME + ' WHERE ID = ''' + @p1 + ''''

Set @p2 = Exec (@sSQL)

e106199
Starting Member

13 Posts

Posted - 2005-12-13 : 14:51:16

Srinika,
unfortunately what you suggested didnt work at all.
And Kristen, i tried what you suggested but i m getting Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
error.
Any ideas?
thank you
-shane

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-13 : 15:37:24

What is not working? first one or the one has output parameter ?
I think the Output Parameter one. Sorry my mistake

OK try this:

Set sSQL = 'select @P2 = myCODE from anotherTable where ID = (SELECT ID2 FROM ' + @TABLENAME + ' WHERE ID = ''' + @p1 + ''''

Exec (@sSQL)
U can always check the results with "Print" to verify u are creating the proper query!!
eg. Print @sSQL gives the contents of @sSQL

The Error u get has @Statement ???
May be u use that name instead of @p2 and may be its defined as text. it should be defined as varchar

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-13 : 16:29:22

Srinika, you can't use an output variable with "exec (@sql)" you need to use Kristen's approach with sp_executesql.

Shane, Kristen's will work if you change
from @strSQL varchar(8000)
to @strSQL nvarchar(4000)

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 18:31:24

"i m getting Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'"

Yeah, SQL tends to want everything in UNICODE, sorry about that. So ... (as TG said) use:

DECLARE @strSQL nvarchar(4000)

Kristen

e106199
Starting Member

13 Posts

Posted - 2005-12-13 : 22:47:42

thank you all,
it worked.
-shane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources