由于存储过程太长,我将前部分省略了,此前的存储过程是组合sql字符串@chvSQLPType
我单独执行EXEC(@chvSQLPType)每次都没有问题。我看问题可能出在以下部分,请大家帮忙DECLARE @chrSQLCursor varchar(2000)
SELECT @chrSQLCursor = 'DECLARE PTypeIDCursor CURSOR FOR
' + @chvSQLPType + ' ORDER BY [UserCode]'
EXEC(@chrSQLCursor)
DECLARE @TypeID varchar(25), @UserCode varchar(26), @FullName varchar(66), @SonNum int, @Unit1 varchar(8), @PrePrice1 numeric(18,4), @PrePrice2 numeric(18,4), @PrePrice3 numeric(18,4), @PrePrice4 numeric(18,4), @PrePrice5 numeric(18,4), @RecPrice money, @IfSerial char(1), @EntryCode varchar(50), @Standard varchar(20), @Type varchar(20), @Area varchar(30), @QTY numeric(18,4), @FactQty numeric(18,4), @CostPrice numeric(18,4), @SalePriceTrack numeric(18,4), @SaleOrderPriceTrack numeric(18,4), @BuyPriceTrack numeric(18,4), @BuyOrderPriceTrack numeric(18,4), @OrderQty numeric(18,4), @CommissionType int --#-01
DECLARE @Unit2 varchar(8), @UnitRate1 numeric(18,4)
SELECT @TypeID = ''
SET NOCOUNT ON -- 返回结果不受影响
-- 建立查询临时表 ( 表中的记录条数将为 intPageSize )
CREATE TABLE #temp (
TypeID varchar(25),
UserCode varchar(26),
FullName varchar(66),
SonNum int,
Unit1 varchar(8),
Unit2 varchar(8),
UnitRate1 numeric(18,4),
PrePrice1 numeric(18,4),
PrePrice2 numeric(18,4),
PrePrice3 numeric(18,4),
PrePrice4 numeric(18,4),
PrePrice5 numeric(18,4),
RecPrice money,
IfSerial char(1), -- 是否采用序列号强制管理;0:不采用;1:采用
EntryCode varchar(50), -- 条形码
Standard varchar(20), -- 规格
Type varchar(20), -- 型号
Area varchar(30), -- 产地
QTY numeric(18,4), -- 账面库存
FactQty numeric(18,4), -- 实际库存
CostPrice numeric(18,4),
--ReturnCostPrice numeric(18,4), -- 返回的拆装单成本价,将根据成本算法计算
SalePriceTrack numeric(18,4), --销售出库单价格
SaleOrderPriceTrack numeric(18,4), --销售订单价格 #-01
BuyPriceTrack numeric(18,4), --采购入库单价格
BuyOrderPriceTrack numeric(18,4), --采购订单价格
OrderQty numeric(18,4), -- 销售订单 或 进货订单的订货数量 #-01
PromotePrice numeric(18,4) -- 商品促销价格
)
DECLARE @chvTypeIDTemp varchar(50) -- 商品编号过渡值
DECLARE @PromotePrice numeric(18,4) -- 商品促销价格
OPEN PTypeIDCursor -- 打开游标
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
FETCH NEXT FROM PTypeIDCursor INTO @TypeID, @UserCode, @FullName, @SonNum, @Unit1, @Unit2, @UnitRate1, @PrePrice1, @PrePrice2, @PrePrice3, @PrePrice4, @PrePrice5, @RecPrice, @IfSerial, @EntryCode, @Standard, @Type, @Area
INSERT INTO #temp (
TypeID,
UserCode,
FullName,
SonNum,
Unit1,
Unit2,
UnitRate1,
PrePrice1,
PrePrice2,
PrePrice3,
PrePrice4,
PrePrice5,
RecPrice,
IfSerial,
EntryCode,
Standard,
Type,
Area,
QTY,
FactQty,
CostPrice,
--ReturnCostPrice,
SalePriceTrack,
SaleOrderPriceTrack,
BuyPriceTrack,
BuyOrderPriceTrack,
OrderQty,
PromotePrice
)
VALUES (
@TypeID,
@UserCode,
@FullName,
@SonNum,
@Unit1,
@Unit2,
@UnitRate1,
@PrePrice1,
@PrePrice2,
@PrePrice3,
@PrePrice4,
@PrePrice5,
@RecPrice,
@IfSerial,
@EntryCode,
@Standard,
@Type,
@Area,
@QTY,
@FactQty,
@CostPrice,
--@ReturnCostPrice,
@SalePriceTrack,
@SaleOrderPriceTrack,
@BuyPriceTrack,
@BuyOrderPriceTrack,
@OrderQty,
@PromotePrice
)
end
end
CLOSE PTypeIDCursor -- 关闭游标
DEALLOCATE PTypeIDCursor -- 释放游标所使用的资源
select * from #temp
DROP TABLE #temp
我单独执行EXEC(@chvSQLPType)每次都没有问题。我看问题可能出在以下部分,请大家帮忙DECLARE @chrSQLCursor varchar(2000)
SELECT @chrSQLCursor = 'DECLARE PTypeIDCursor CURSOR FOR
' + @chvSQLPType + ' ORDER BY [UserCode]'
EXEC(@chrSQLCursor)
DECLARE @TypeID varchar(25), @UserCode varchar(26), @FullName varchar(66), @SonNum int, @Unit1 varchar(8), @PrePrice1 numeric(18,4), @PrePrice2 numeric(18,4), @PrePrice3 numeric(18,4), @PrePrice4 numeric(18,4), @PrePrice5 numeric(18,4), @RecPrice money, @IfSerial char(1), @EntryCode varchar(50), @Standard varchar(20), @Type varchar(20), @Area varchar(30), @QTY numeric(18,4), @FactQty numeric(18,4), @CostPrice numeric(18,4), @SalePriceTrack numeric(18,4), @SaleOrderPriceTrack numeric(18,4), @BuyPriceTrack numeric(18,4), @BuyOrderPriceTrack numeric(18,4), @OrderQty numeric(18,4), @CommissionType int --#-01
DECLARE @Unit2 varchar(8), @UnitRate1 numeric(18,4)
SELECT @TypeID = ''
SET NOCOUNT ON -- 返回结果不受影响
-- 建立查询临时表 ( 表中的记录条数将为 intPageSize )
CREATE TABLE #temp (
TypeID varchar(25),
UserCode varchar(26),
FullName varchar(66),
SonNum int,
Unit1 varchar(8),
Unit2 varchar(8),
UnitRate1 numeric(18,4),
PrePrice1 numeric(18,4),
PrePrice2 numeric(18,4),
PrePrice3 numeric(18,4),
PrePrice4 numeric(18,4),
PrePrice5 numeric(18,4),
RecPrice money,
IfSerial char(1), -- 是否采用序列号强制管理;0:不采用;1:采用
EntryCode varchar(50), -- 条形码
Standard varchar(20), -- 规格
Type varchar(20), -- 型号
Area varchar(30), -- 产地
QTY numeric(18,4), -- 账面库存
FactQty numeric(18,4), -- 实际库存
CostPrice numeric(18,4),
--ReturnCostPrice numeric(18,4), -- 返回的拆装单成本价,将根据成本算法计算
SalePriceTrack numeric(18,4), --销售出库单价格
SaleOrderPriceTrack numeric(18,4), --销售订单价格 #-01
BuyPriceTrack numeric(18,4), --采购入库单价格
BuyOrderPriceTrack numeric(18,4), --采购订单价格
OrderQty numeric(18,4), -- 销售订单 或 进货订单的订货数量 #-01
PromotePrice numeric(18,4) -- 商品促销价格
)
DECLARE @chvTypeIDTemp varchar(50) -- 商品编号过渡值
DECLARE @PromotePrice numeric(18,4) -- 商品促销价格
OPEN PTypeIDCursor -- 打开游标
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
FETCH NEXT FROM PTypeIDCursor INTO @TypeID, @UserCode, @FullName, @SonNum, @Unit1, @Unit2, @UnitRate1, @PrePrice1, @PrePrice2, @PrePrice3, @PrePrice4, @PrePrice5, @RecPrice, @IfSerial, @EntryCode, @Standard, @Type, @Area
INSERT INTO #temp (
TypeID,
UserCode,
FullName,
SonNum,
Unit1,
Unit2,
UnitRate1,
PrePrice1,
PrePrice2,
PrePrice3,
PrePrice4,
PrePrice5,
RecPrice,
IfSerial,
EntryCode,
Standard,
Type,
Area,
QTY,
FactQty,
CostPrice,
--ReturnCostPrice,
SalePriceTrack,
SaleOrderPriceTrack,
BuyPriceTrack,
BuyOrderPriceTrack,
OrderQty,
PromotePrice
)
VALUES (
@TypeID,
@UserCode,
@FullName,
@SonNum,
@Unit1,
@Unit2,
@UnitRate1,
@PrePrice1,
@PrePrice2,
@PrePrice3,
@PrePrice4,
@PrePrice5,
@RecPrice,
@IfSerial,
@EntryCode,
@Standard,
@Type,
@Area,
@QTY,
@FactQty,
@CostPrice,
--@ReturnCostPrice,
@SalePriceTrack,
@SaleOrderPriceTrack,
@BuyPriceTrack,
@BuyOrderPriceTrack,
@OrderQty,
@PromotePrice
)
end
end
CLOSE PTypeIDCursor -- 关闭游标
DEALLOCATE PTypeIDCursor -- 释放游标所使用的资源
select * from #temp
DROP TABLE #temp
OPEN PTypeIDCursor -- 打开游标
FETCH NEXT FROM PTypeIDCursor INTO @TypeID, @UserCode, @FullName, @SonNum,
@Unit1, @Unit2, @UnitRate1, @PrePrice1, @PrePrice2, @PrePrice3, @PrePrice4, @PrePrice5,
@RecPrice, @IfSerial, @EntryCode, @Standard, @Type, @Area
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
INSERT INTO #temp (
TypeID,
UserCode,
FullName,
SonNum,
Unit1,
Unit2,
UnitRate1,
PrePrice1,
PrePrice2,
PrePrice3,
PrePrice4,
PrePrice5,
RecPrice,
IfSerial,
EntryCode,
Standard,
Type,
Area,
QTY,
FactQty,
CostPrice,
--ReturnCostPrice,
SalePriceTrack,
SaleOrderPriceTrack,
BuyPriceTrack,
BuyOrderPriceTrack,
OrderQty,
PromotePrice
)
VALUES (
@TypeID,
@UserCode,
@FullName,
@SonNum,
@Unit1,
@Unit2,
@UnitRate1,
@PrePrice1,
@PrePrice2,
@PrePrice3,
@PrePrice4,
@PrePrice5,
@RecPrice,
@IfSerial,
@EntryCode,
@Standard,
@Type,
@Area,
@QTY,
@FactQty,
@CostPrice,
--@ReturnCostPrice,
@SalePriceTrack,
@SaleOrderPriceTrack,
@BuyPriceTrack,
@BuyOrderPriceTrack,
@OrderQty,
@PromotePrice
)
FETCH NEXT FROM PTypeIDCursor INTO @TypeID, @UserCode, @FullName, @SonNum,
@Unit1, @Unit2, @UnitRate1, @PrePrice1, @PrePrice2, @PrePrice3, @PrePrice4, @PrePrice5,
@RecPrice, @IfSerial, @EntryCode, @Standard, @Type, @Area
end
end
CLOSE PTypeIDCursor -- 关闭游标
DEALLOCATE PTypeIDCursor -- 释放游标所使用的资源
select * from #temp
DROP TABLE #temp
go
你确保你的@chvSQLPType每次都能抓到记录?
这是管家婆的数据结构,1、从产品数据中提取记录(记录必须满足特定要求如:权限范围、当前记录的父记录等等)
这个过程得到@chvSQLPType,无错误。
2、将chvSQLPType记录使用游标,当游标游到每一条产品记录时,马上获取本产品的其他相关资料(如:到订单中获取本产品的订货数量、到配置表中获取本产品是否采用价格跟踪、到库存表中获取账面库存、获取产品本期的促销价格等等)
3、将以上的每条记录插入到新建的零时表#temp中,
4、读取#temp