create procedure test
begin
--按条件查询库存表,并保存到临时表中
create table #tmp(
pc_id int default 0 not null,
dpt_id int default 0 not null,
shelf_code varchar(20) null,
goods_id int default 0 not null,
offer_id int default 0 not null,
goods_sl numeric(9,2) default 0 not null,
buy_price money default 0 not null,
qty numeric(18,4) default 0 not null,
zt_qty numeric(18,4) default 0 not null
)
select @str='insert into #tmp(pc_id, dpt_id, shelf_code, goods_id, offer_id, goods_sl, buy_price, qty, zt_qty) ' +
'select a.pc_id, a.dpt_id, a.shelf_code, a.goods_id, a.offer_id, a.goods_sl, a.buy_price, a.rk_qty-a.ck_qty as qty, a.zt_qty '+
'from tbl_stock_qty a, tbl_goods_info b, tbl_kind_info c where a.goods_id=b.goods_id and b.kind_id=c.kind_id '
exec(@str)
select * from #tmp
end目的是把查询结果保存到#tmp里面, 然后再根据条件对#tmp里面内容进行不同的分组查询. 我在最后执行select * from #tmp,但是在执行过程test的时候#tmp中没数据.@str这个是根据输入条件拼出来的语句.
begin
--按条件查询库存表,并保存到临时表中
create table #tmp(
pc_id int default 0 not null,
dpt_id int default 0 not null,
shelf_code varchar(20) null,
goods_id int default 0 not null,
offer_id int default 0 not null,
goods_sl numeric(9,2) default 0 not null,
buy_price money default 0 not null,
qty numeric(18,4) default 0 not null,
zt_qty numeric(18,4) default 0 not null
)
select @str='insert into #tmp(pc_id, dpt_id, shelf_code, goods_id, offer_id, goods_sl, buy_price, qty, zt_qty) ' +
'select a.pc_id, a.dpt_id, a.shelf_code, a.goods_id, a.offer_id, a.goods_sl, a.buy_price, a.rk_qty-a.ck_qty as qty, a.zt_qty '+
'from tbl_stock_qty a, tbl_goods_info b, tbl_kind_info c where a.goods_id=b.goods_id and b.kind_id=c.kind_id '
exec(@str)
select * from #tmp
end目的是把查询结果保存到#tmp里面, 然后再根据条件对#tmp里面内容进行不同的分组查询. 我在最后执行select * from #tmp,但是在执行过程test的时候#tmp中没数据.@str这个是根据输入条件拼出来的语句.
'from tbl_stock_qty a, tbl_goods_info b, tbl_kind_info c where a.goods_id=b.goods_id and b.kind_id=c.kind_id ')语法没错,检查是否存在符合条件的数据。
我print @str出拉, 然后执行代码, 里面是有数据的.
pc_id int default 0 not null,
dpt_id int default 0 not null,
shelf_code varchar(20) null,
goods_id int default 0 not null,
offer_id int default 0 not null,
goods_sl numeric(9,2) default 0 not null,
buy_price money default 0 not null,
qty numeric(18,4) default 0 not null,
zt_qty numeric(18,4) default 0 not null
)
select @str=insert into #tmp(pc_id, dpt_id, shelf_code, goods_id, offer_id, goods_sl, buy_price, qty, zt_qty
select a.pc_id, a.dpt_id, a.shelf_code, a.goods_id, a.offer_id, a.goods_sl, a.buy_price, a.rk_qty-a.ck_qty as qty, a.zt_qty
from tbl_stock_qty a, tbl_goods_info b, tbl_kind_info c where a.goods_id=b.goods_id and b.kind_id=c.kind_idselect * from #tmp
gocreate procedure test
as
begin
--按条件查询库存表,并保存到临时表中
create table #tmp(
id int )
declare @str nvarchar(1000)
select @str='insert into #tmp(id)
select id from sysobjects '
exec(@str)
select * from #tmp
end
goexec test
go
drop proc test
--select @str=insert...
insert...
而条件不满足的时候@wh是没值的,在初始值后select @wh=''就可以了.非常感谢大家的帮助.