Declaring table variable - SQL Server Forums

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

Transact-SQL (2000)

Declaring table variable

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-04-15 : 08:58:41

Guys,

I am trying compile below function where I pass tablename as variable.

CREATE FUNCTION [dbo].[GetNextValue] (@tablename varchar(30) )
RETURNS int AS
BEGIN
declare @value int
truncate table @tablename
INSERT INTO @tablename (FNAME) VALUES ('SMITH')
select @value = id from @tablename
return @value
END

I get the following message

Msg 1087, Level 15, State 2, Procedure GetNextValue, Line 7
Must declare the table variable "@tablename".

Any suggestions/inputs to get around this would be helpful

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 09:05:19

Duplicate thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82122