请问一个存储过程的问题。怎么把传入参数作为Sql语句所查表的表名。

[lihongxing2002]

我有以下存储过程：总是提示：
必须声明变量 '@name'。
第 7 行: ')' 附近有语法错误。

CREATE PROCEDURE ImportBaseDataQT
@name varchar(100)
AS
DECLARE @ErrorVar int
BEGIN TRANSACTION

insert into VSites (地点) select distinct 地点 from @name where (地点 not in (select * from VSites))
insert into VLocations (地点,位置) select 地点,位置 from @name where (地点 not in (select * from VSites))

SELECT @ErrorVar = @@error
IF (@ErrorVar <>0)
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END
ELSE
COMMIT TRANSACTION
RETURN 0
GO

但是当我不用传入参数@name,而是换成表名的时候，就没有语法错误。请问该怎么解决？因为我要的表名是个临时表，表的名字是会变得。

[lihongxing2002]

上面存储过程我多写了一句，更正如下：
CREATE PROCEDURE ImportBaseDataQT
@name varchar(100)
AS
DECLARE @ErrorVar int
BEGIN TRANSACTION

insert into VSites (地点) select distinct 地点 from @name where (地点 not in (select * from VSites))

SELECT @ErrorVar = @@error
IF (@ErrorVar <>0)
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END
ELSE
COMMIT TRANSACTION
RETURN 0
GO

[gaobochina]

declare @str varchar(4000)
declare @tablename varchar(20)
set @tablename='表名'
set @str='select * from '+@tablename
select @str
exec @str

[gaobochina]

哦
对不起
最后一句忘加括号了
exec (@str)

[zjcxc]

表名不能直接使用变量,改成下面的这样就可以了:


CREATE PROCEDURE ImportBaseDataQT
@name varchar(100)
AS
DECLARE @ErrorVar int
BEGIN TRANSACTION

exec('insert into VSites (地点) select distinct 地点 from '+@name+' where (地点 not in (select * from VSites))'

SELECT @ErrorVar = @@error
IF (@ErrorVar <>0)
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END
ELSE
COMMIT TRANSACTION
RETURN 0
GO

[zjcxc]

上面的写少了一个括号,改一下:

CREATE PROCEDURE ImportBaseDataQT
@name varchar(100)
AS
DECLARE @ErrorVar int
BEGIN TRANSACTION

exec('insert into VSites (地点) select distinct 地点 from '+@name+' where (地点 not in (select * from VSites))') --将你原来这句改为用exec执行就行了

SELECT @ErrorVar = @@error
IF (@ErrorVar <>0)
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END
ELSE
COMMIT TRANSACTION
RETURN 0
GO

[pengdali]

CREATE PROCEDURE ImportBaseDataQT
@name varchar(100)
AS
DECLARE @ErrorVar int
BEGIN TRANSACTION

exec('insert into VSites (地点) select distinct 地点 from '+@name+' where (地点 not in (select * from VSites))
insert into VLocations (地点,位置) select 地点,位置 from '+@name+' where (地点 not in (select * from VSites))')

SELECT @ErrorVar = @@error
IF (@ErrorVar <>0)
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END
ELSE
COMMIT TRANSACTION
RETURN 0
GO

[pengdali]

CREATE PROCEDURE ImportBaseDataQT
@name varchar(100)
AS
DECLARE @ErrorVar int
BEGIN TRANSACTION

exec('insert into VSites (地点) select distinct 地点 from '+@name+' where (地点 not in (select * from VSites))')
SELECT @ErrorVar = @@error
exec('insert into VLocations (地点,位置) select 地点,位置 from '+@name+' where (地点 not in (select * from VSites))')
IF (@ErrorVar <>0 or @@error<>0)
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END
ELSE
COMMIT TRANSACTION
RETURN 0
GO