请大家帮我把下面的存储过程改成如何同表名是动态的形成,还有@pcsl如何累加CREATE PROCEDURE SBDHSCJH1 (@jz nvarchar(10),@zhy nvarchar(50),@danwei nvarchar(20), @xmmc nvarchar(50),@time1 nvarchar(200),
@pcsl int output )
AS
declare @ht_key nvarchar(100), @jzh nvarchar(10), @zhuany nvarchar(50),@dw nvarchar(10)
declare cur cursor local for select ht_key from Eq_htinfo where ht_jzh=@jz and ht_zhuanye=@zhy and ht_jldw=@danwei and ht_pnum=@xmmc
open cur
fetch next from cur into @ht_key
while @@fetch_status=0
begin
------------------------计算已排产数量---------------------------------
set @pcsl =@pcsl+isnull( (select ht_sl from Eq_htinfo where ht_pnum=@xmmc and ht_jzh=@jz and ht_key=@ht_key and ht_key in
(select ht_key from Eq_prodplan where pdp_pnum=@xmmc+ @time1)) ,0)
fetch next from cur into @ht_key
end
close cur
deallocate cur
return
GO
@pcsl int output )
AS
declare @ht_key nvarchar(100), @jzh nvarchar(10), @zhuany nvarchar(50),@dw nvarchar(10)
declare cur cursor local for select ht_key from Eq_htinfo where ht_jzh=@jz and ht_zhuanye=@zhy and ht_jldw=@danwei and ht_pnum=@xmmc
open cur
fetch next from cur into @ht_key
while @@fetch_status=0
begin
------------------------计算已排产数量---------------------------------
set @pcsl =@pcsl+isnull( (select ht_sl from Eq_htinfo where ht_pnum=@xmmc and ht_jzh=@jz and ht_key=@ht_key and ht_key in
(select ht_key from Eq_prodplan where pdp_pnum=@xmmc+ @time1)) ,0)
fetch next from cur into @ht_key
end
close cur
deallocate cur
return
GO
这个好说,用SET @PCSL=@PCSL+你要的
前面那个实在没看懂
给你个示例:
declare @tbleName nvarchar(200)
declare @SqlStr varchar(8000)
set @tbleName='Table1'
set @SqlStr='select * from '+@tbleName
exec(@SqlStr)
declare @SqlStr varchar(8000)
set @tbleName='a'
set @SqlStr='select * from '+@tbleName+'1' --最后组成的表名是'a1'
exec(@SqlStr)
@pcsl int output )
AS
DECLARE @sql NVARCHAR(1000)
SET @sql='
select @pcsl=isnull(sum(ht_sl),0) from '+@TbName+'
where ht_pnum='+@xmmc +'
and ht_jzh='+@jz+'
and ht_key IN (select ht_key from '+@TbName+' where ht_jzh='+@jz+' and ht_zhuanye='+@zhy+' and ht_jldw='+@danwei+' and ht_pnum='+@xmmc+')
and ht_key in (select ht_key from Eq_prodplan where pdp_pnum='+@xmmc+ @time1+')'EXEC sp_executesql @sql,N'@pcsl int out',@pcsl OUT
RETURN
create table aaa(a int)
insert into aaa select 1
go
Create table bbb(b int)
insert into bbb select 1Create proc SeachTable
@tb nvarchar(500)
as
declare @SqlStr varchar(8000)
set @SqlStr='select * from aaa inner join '+@tb+' as b1 on b1.b=aaa.a'
exec(@SqlStr)--执行
SeachTable 'bbb'--执行结果
/*
a b
1 1
*/
declare cur cursor local for select ht_key from Eq_htinfo where ht_jzh=@jz and ht_zhuanye=@zhy and ht_jldw=@danwei and ht_pnum=@xmmc
open cur
用游标可以这样:
exec('declare cur cursor local for select ht_key from '+@TBname+' where ht_jzh='+@jz+' and ht_zhuanye='+@zhy+' and ht_jldw='+@danwei+' and ht_pnum='+@xmmc)
open cur
你可先把他们和起来再赋值给他
alter PROCEDURE SBDHSCJH1 (@TbName nvarchar(20),@jz nvarchar(10),@zhy nvarchar(50),@danwei nvarchar(20), @xmmc nvarchar(50),@time1 nvarchar(200),
@pcsl int output )
AS
DECLARE @sql NVARCHAR(1000)
set @table1=@xmmc+'_Eq_htinfo'
exec('declare cur cursor local for select ht_key from '+@table1+' where ht_jzh='+@jz+' and ht_zhuanye='+@zhy+' and ht_jldw='+@danwei+' and ht_pnum='+@xmmc)
open cur
SET @sql='
select @pcsl=isnull(sum(ht_sl),0) from '+@TbName+'
where ht_pnum='+@xmmc +'
and ht_jzh='+@jz+'
and ht_key IN (select ht_key from '+@TbName+' where ht_jzh='+@jz+' and ht_zhuanye='+@zhy+' and ht_jldw='+@danwei+' and ht_pnum='+@xmmc+')
and ht_key in (select ht_key from Eq_prodplan where pdp_pnum='+@xmmc+ @time1+')'EXEC sp_executesql @sql,N'@pcsl int out',@pcsl OUT
RETURN
像这样就会出错
9楼写的就是去掉了游标的写法,定义游标的那个select语句写在@sql中的一个in后面了
@pcsl int output )
AS
DECLARE @sql NVARCHAR(1000)
declare @table1 nvarchar(100)
set @table1=@xmmc+'_Eq_htinfo'SET @sql='
select @pcsl=isnull(sum(ht_sl),0) from '+@TbName+'
where ht_pnum='+@xmmc +'
and ht_jzh='+@jz+'
and ht_key IN (select ht_key from '+@table1+' where ht_jzh='+@jz+' and ht_zhuanye='+@zhy+' and ht_jldw='+@danwei+' and ht_pnum='+@xmmc+')
and ht_key in (select ht_key from Eq_prodplan where pdp_pnum='+@xmmc+ @time1+')'EXEC sp_executesql @sql,N'@pcsl int out',@pcsl OUT
RETURN
set @pcsl=@pcsl+ 这句的执行结果 SET @sql='
select @pcsl=isnull(sum(ht_sl),0) from '+@TbName+'
where ht_pnum='+@xmmc +'
and ht_jzh='+@jz+'
and ht_key IN (select ht_key from '+@table1+' where ht_jzh='+@jz+' and ht_zhuanye='+@zhy+' and ht_jldw='+@danwei+' and ht_pnum='+@xmmc+')
and ht_key in (select ht_key from Eq_prodplan where pdp_pnum='+@xmmc+ @time1+')'EXEC sp_executesql @sql,N'@pcsl int out',@pcsl OUT 如何写呢
你的游标是一行行去遍历这个结果[(select ht_key from '+@table1+' where ht_jzh='+@jz+' and ht_zhuanye='+@zhy+' and ht_jldw='+@danwei+' and ht_pnum='+@xmmc+')
]中的ht_key ,从而用set @pcsl=@pcsl+得到的结果来得到最后的数据
而用@sql一条语句在其中将[ and ht_key IN (select ht_key from '+@table1+' where ht_jzh='+@jz+' and ht_zhuanye='+@zhy+' and ht_jldw='+@danwei+' and ht_pnum='+@xmmc+') ]这个条件查询得到的结果一起sum,
两种算的结果不是同一个东西吗?