SQL 2000 stored proc TABLE data type error

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)

SQL 2000 stored proc TABLE data type error

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-15 : 10:33:02

Michael writes "Hello -

I'm trying to utilize the new SQL 2000's TABLE data type & have spent a lot of time trying to make this simple test to work to no avail, please help! The error I keep getting back is
ERROR: Must declare the variable '@Spec'
-----------------------------------------

Declare @sql nvarchar(1000)
Declare @Spec TABLE ( myUserName nvarchar(50) NOT NULL )

set @sql = 'insert into ' + @Spec + ' select UserName from t_users '

exec(@sql)

-------------------------------------

Thank you!!"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-15 : 10:48:31

Declare @sql nvarchar(1000)
Declare @Spec TABLE ( myUserName nvarchar(50) NOT NULL )

INSERT INTO @Spec
SELECT UserName FROM t_users

Nazim
A custom title

1408 Posts

Posted - 2002-05-15 : 10:51:50

What are you tring to do with this statement. you have to remember you cant use a table variable like a normal variable . you ought to treat it like a table object

if a give a statement it is illogical
@spec=10
and So is this

set @sql = 'insert into ' + @Spec + ' select UserName from t_users '

but something like this is perfectly ok

select * from @spec

set @sql='insert into @spec select username from t_users'(probably this is what you are trying to do)

HTH

SNIPED!!!!!!!

--------------------------------------------------------------

Edited by - Nazim on 05/15/2002 10:52:39

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources