CREATE PROCEDURE test @myname varchar(1000) AS exec('select * from '+@myname) GO------------------ exec test '你的表名'
如果SQL 语句中使用一个变量来代替对象名,这样的动态SQL 语句必须使用 EXEC来执行。exec test '你的表名' 或exec test @myname = '你的表名'
大力哥,那我下面这个存储过程那个地方有错误啊?CREATE PROCEDURE Display_Data @ClassName char(10),@TempDB char(10) AS declare @LClass varchar(20) declare MyCursor cursor for select 小类名称 from ClassName fetch next from MyCursor into @LClass while (@@FETCH_STATUS=0) begin select * into TempDB from LClass fetch next from MyCursor into @LClass end close MyCursor deallocate MyCursor select * from TempDB GO
1、select * into 你知道是做什么的吗?你的tempDB能被多次新建吗? 2、你的lclass表和@lclass有什么关系?估计lclass是表名变量 3、因为你写的错误太多,我不能明确你的目的是什么? --估计这是你想要的: CREATE PROCEDURE Display_Data @ClassName varchar(10),@TempDB varchar(10) AS declare @LClass varchar(20) exec('declare MyCursor cursor for select 小类名称 from '+@ClassName) fetch next from MyCursor into @LClass while (@@FETCH_STATUS=0) begin exec('insert '+@tempdb+' select * from '+@LClass) fetch next from MyCursor into @LClass end close MyCursor deallocate MyCursorexec('select * from '+@TempDB) GO
as
declare @Temp char(100)
set @temp='Select * from' + @mynameexec(@temp)
go
@myname varchar(1000)
AS
exec('select * from '+@myname)
GO------------------
exec test '你的表名'
EXEC来执行。exec test '你的表名' 或exec test @myname = '你的表名'
@ClassName char(10),@TempDB char(10)
AS
declare @LClass varchar(20)
declare MyCursor cursor for select 小类名称 from ClassName
fetch next from MyCursor into @LClass
while (@@FETCH_STATUS=0)
begin
select * into TempDB from LClass
fetch next from MyCursor into @LClass
end
close MyCursor
deallocate MyCursor
select * from TempDB
GO
2、你的lclass表和@lclass有什么关系?估计lclass是表名变量
3、因为你写的错误太多,我不能明确你的目的是什么?
--估计这是你想要的:
CREATE PROCEDURE Display_Data
@ClassName varchar(10),@TempDB varchar(10)
AS
declare @LClass varchar(20)
exec('declare MyCursor cursor for select 小类名称 from '+@ClassName)
fetch next from MyCursor into @LClass
while (@@FETCH_STATUS=0)
begin
exec('insert '+@tempdb+' select * from '+@LClass)
fetch next from MyCursor into @LClass
end
close MyCursor
deallocate MyCursorexec('select * from '+@TempDB)
GO