sql 中的 store Procedures 问题

问题：

create proc ExistsID
@TableName varchar(20),
@ID int
as
if(not Exists(select name from sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1))
return 1
else

if (Exists(select * from @TableName where id = @ID))
return 0
else
return 2
end
上面这段话有什么问题
执行时报
Server: Msg 137, Level 15, State 2, Procedure ExistsID1, Line 9
Must declare the variable '@TableName'.
大家帮忙看看
---------------------------------------------------------------

解答：

当然不能这么用啦,要用动态语句实现动态表名查询
create proc ExistsID
(
@TableName varchar(20),
@ID int
)
as
declare @SqlString varchar(200)
if(not Exists(select name from sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1))
return 1
else
begin
set @SqlString=N'if (Exists(select * from '+@TableName+' where id ='+cast(@ID as varchar(10))+')) return 0 else return 2 '
exec(@SqlString)
end

---------------------------------------------------------------
create proc ExistsID
(
@TableName varchar(20),
@ID int
)
as
declare @count int
if(not Exists(select name from sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1))
return 1
else
begin
exec sp_executesql N'select @i = count(*) from '+@TableName+' where id = '+@ID+' ',
N'@i int output',
@count output
if (@count > 0)
return 0
else
return 2
end

我仔细测试了一下.
@@rowcount确实是当前连接有效

如果是这样的话,那么你的方法最好.
不过我的语句肯定不会错.
-----------------------------------------------------------------------------
谢谢大家，第一次写存储过程，不好意思
另外，我也测试出一种方法，本质上和 kkforvb(KK)的一样，
这里列出来，仅供参考
create proc ExistsID
@TableName varchar(20),
@ID int
as
if(not Exists(select name from sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1))
return 1
else
Begin
print object_name(object_id(@TableName))
Declare @strSql varchar(100)
set @strSql = 'select id from ' + @TableName + ' where id=' + cast(@ID as varchar)
print @strSql
ExeC(@strSql)
if (@@rowCount > 0)
return 0
else
return 2
end