过程中,对临时表查询的问题.
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这个是根据输入条件拼出来的语句.

------解决方案--------------------
--try

exec ( '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 ')

语法没错，检查是否存在符合条件的数据。

------解决方案--------------------
--try，看看结果

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

select * from #tmp

------解决方案--------------------
--应该没有问题， 看下面的测试

use tempdb
go

create procedure test
as
begin
--按条件查询库存表,并保存到临时表中
create table #tmp(
id int )
declare @str nvarchar(1000)
select @str= 'insert into #tmp(id)
select id from sysobjects '