1。这个如果只是传入固定某笔很简单呀!
直接在表select出来两个字段@a = a,@b=b,然后调用exec dt1 @a,@b即可2。如果是要整个表传入
那么就最好原来的表要有一个序号,写个循环,对每一笔a,b调用exec dt1 @a,@b,直到全部搜索完即可
直接在表select出来两个字段@a = a,@b=b,然后调用exec dt1 @a,@b即可2。如果是要整个表传入
那么就最好原来的表要有一个序号,写个循环,对每一笔a,b调用exec dt1 @a,@b,直到全部搜索完即可
(dt1 'a',1) union all (dt1 'b',2) union all (dt1 'c',3) union all ……只不过参数来自表
id a b
-------------
1 a 1
2 b 2
3 c 3
……
……
……
declare @canshu2 int
declare ac cursor for select a,b from department
open ac
fetch next from ac into @canshu1,@canshu2
WHILE @@FETCH_STATUS = 0
begin
exec('dt1 '+@canshu1+','+@canshu2+'')
fetch next from ac into @canshu1,@canshu2
end
CLOSE ac
DEALLOCATE ac
这样出来是一条一条的,不是一整张表!!!如何变成一整张表???
declare @canshu2 int
declare @sql varchar(8000)
set @sql=''
declare ac cursor for select a,b from department
open ac
fetch next from ac into @canshu1,@canshu2
WHILE @@FETCH_STATUS = 0
begin
set @sql=@sql+'union all select * from openrowset(''sqloledb'',''localhost'';''sa'';'''',''exec p_1 '+ @canshu1+','+cast(@canshu2 as varchar(20))+''') '
fetch next from ac into @canshu1,@canshu2
end
set @sql=right(@sql,len(@sql)-10)
exec(@sql)
CLOSE ac
DEALLOCATE ac
as
select @a1 as c1,@a2 as c2
godeclare @sql varchar(8000)
declare @canshu1 varchar(10),@canshu2 int
select @canshu1='1',@canshu2=2
set @sql='select * from openrowset(''sqloledb'',''localhost'';''sa'';'''',''exec p_1 '+ @canshu1+','+cast(@canshu2 as varchar(20))+''')'
set @sql=@sql+ 'union all select * from openrowset(''sqloledb'',''localhost'';''sa'';'''',''exec p_1 '+ @canshu1+','+cast(@canshu2 as varchar(20))+''')'
exec(@sql)/*
c1 c2
---------- -----------
1 2
1 2*/
[OLE/DB provider returned message: 多步 OLE DB 操作产生错误。请检查每个 OLE DB 状态值。没有工作被完成。]
CREATE procedure p_1(@a1 varchar(10),@a2 int)
as
declare @bb int
set @bb=@a2+10
select @a1 as c1,@bb as c2
GO
也就是里面定义了变量,进行了计算,就会出现上面的错误!!!
CREATE procedure p_1(@a1 varchar(10),@a2 int)
as
declare @bb int
select @a1 as c1,@a2 as c2
GO提的问题简化了,实际上我的那个存储过程很复杂,经过了大量的计算,所以其中定义变量是在所难免的