这是我的一个存储过程,作用在两个不同的数据库中,第一个数据库正常执行,但在第二个数据库中执行的时候通常要好几分钟才能执行完毕,请学长们帮我看看谢谢!
DECLARE @count int
DECLARE @NO varchar(10)
DECLARE @NAME VARCHAR(30)
DECLARE @PRICE MONEY
DECLARE @SALE_PRICE MONEY
DECLARE @STOCK_QTY_SUM INT
DECLARE @SALE_QNTY INT
DECLARE @OPER_DATE DATETIME
DECLARE CURSOR1 CURSOR FOR select DISTINCT pos_t_saleflow.item_no from pos_t_saleflow where DATEDIFF(day, pos_t_saleflow.oper_date,GETDATE()) < 7 --查询7天之内销售商品流水
OPEN cursor1
FETCH NEXT FROM cursor1 into @no while @@fetch_status = 0
begin
select @STOCK_QTY_SUM=sum(stock_qty) from wh_stock where item_no = @no --根据条码查找库存数量
select @SALE_QNTY=sum(sale_qty),@OPER_DATE=oper_date from pos_t_saleflow where item_no = @no AND DATEDIFF(day, pos_t_saleflow.oper_date,GETDATE()) < 7 GROUP BY OPER_DATE--7天之内销售商品的总数
select @NAME=item_name,@PRICE=in_price,@SALE_PRICE=sale_price from item where item_no = @no--根据条码查找商品的名称、进货价、零售价
insert into item_sale_status(item_no,item_name,item_stock_qty,item_sale_qnty,item_info_price,item_sale_price/*,item_oper_date*/)
values (@no,@NAME,@STOCK_QTY_SUM,@SALE_QNTY,@PRICE,@SALE_PRICE/*,@OPER_DATE*/)--将查找到得结果一条一条添加到item_sale_status表中
FETCH NEXT FROM cursor1 into @no
end
close cursor1 --关闭游标
deallocate cursor1
DECLARE @count int
DECLARE @NO varchar(10)
DECLARE @NAME VARCHAR(30)
DECLARE @PRICE MONEY
DECLARE @SALE_PRICE MONEY
DECLARE @STOCK_QTY_SUM INT
DECLARE @SALE_QNTY INT
DECLARE @OPER_DATE DATETIME
DECLARE CURSOR1 CURSOR FOR select DISTINCT pos_t_saleflow.item_no from pos_t_saleflow where DATEDIFF(day, pos_t_saleflow.oper_date,GETDATE()) < 7 --查询7天之内销售商品流水
OPEN cursor1
FETCH NEXT FROM cursor1 into @no while @@fetch_status = 0
begin
select @STOCK_QTY_SUM=sum(stock_qty) from wh_stock where item_no = @no --根据条码查找库存数量
select @SALE_QNTY=sum(sale_qty),@OPER_DATE=oper_date from pos_t_saleflow where item_no = @no AND DATEDIFF(day, pos_t_saleflow.oper_date,GETDATE()) < 7 GROUP BY OPER_DATE--7天之内销售商品的总数
select @NAME=item_name,@PRICE=in_price,@SALE_PRICE=sale_price from item where item_no = @no--根据条码查找商品的名称、进货价、零售价
insert into item_sale_status(item_no,item_name,item_stock_qty,item_sale_qnty,item_info_price,item_sale_price/*,item_oper_date*/)
values (@no,@NAME,@STOCK_QTY_SUM,@SALE_QNTY,@PRICE,@SALE_PRICE/*,@OPER_DATE*/)--将查找到得结果一条一条添加到item_sale_status表中
FETCH NEXT FROM cursor1 into @no
end
close cursor1 --关闭游标
deallocate cursor1
解决方案 »
- 请问win7下Sql05用sql身份验证能连接,windows身份验证则出现18456错误
- SSIS动态创建txt文件并导出数据出现错误
- SQL查询遇到的麻烦
- SQL Server日志问题
- 请教,我的sql循环套循环出了问题,请大侠帮个忙。
- ★ 急求一sqL语句,先鞠躬请您进来看看。
- SQLServer惊天大BUG? [code=SQL] select * from table_name where col_date<='2010-6-28
- mysql数据库怎么批量修改
- 求助......在线等.........该如何建立索引...
- 有关like 大问题!
- 在sa用户下有一张表格Menu被隐藏
- 简单的存储过程,但是总是出错。请高手了。
<重新组织和重新生成索引sp_RefreshIndex>
http://www.cnblogs.com/wghao/archive/2012/12/27/2835971.html
@OPER_DATE Group 具有不确定性,如果DATE有多个值的话