一个让人几近崩溃的存储过程 @@
有这样三个表：
表OrderD: 列：OrderID char(10),
SKU char(10),
表OrderH: 列：OrderID char(10),
Type char(1),
OrderDate datetime,
表SKU: 列：SKU char(10),
SKUName nvarchar(50),
Memo nvarchar(200),
Spec varchar(50),
Size varchar(20),
Color varchar(20),
OrderQty int,
创建一个存储过程 [dbo][GetSKUSale]
要求：
当输入:@SKU,@SKUName,@MinOrderDate, @MaxOrderDate
输出：SKU.SKU,SKU.SKUName,OrderH.Type,OrderH.OrderID,@OrderQty,OrderH.OrderDate,SKU.Color,SKU.Size,SKU.Spec,SKU.Memo
备注：@SKU,@SKUName 可能为 ' '; @MinOrderDate, @MaxOrderDate 必须要输入确切的日期。
我写了这样一段存储过程，为何运行时总是有错误，麻烦XDJM 们帮忙看下~
CREATE proc [dbo].[GetSKUSalesDetailsBySKU]
@SKU char(10),
@SKUName nvarchar(50),
@MinOrderDate datetime,
@MaxOrderDate datetime
as
declare @where varchar(500)
set @where= ' Where POS.SKU.SKU=POS.OrderD.SKU AND POS.OrderD.OrderID=POS.OrderH.OrderID AND POS.OrderH.OrderDate between ' ' '+ CONVERT(varchar(50), @MinOrderDate)+ ' ' ' and ' ' '+CONVERT(varchar(50),@MaxOrderDate)+ ' ' ' '
if @SKU <> ' ' begin set @where=@where+ ' and POS.SKU.SKU= '+@SKU end
if @SKUName <> ' ' begin set @where=@where+ ' and POS.SKU.SKUName= '+@SKUName end
declare @OrderQty varchar(500)
set @OrderQty= 'select sum(POS.SKU.OrderQty) from POS.SKU '+@where
declare @sql varchar(1000)
set @sql= 'select POS.SKU.SKU,POS.SKU.SKUName,POS.OrderH.Type,POS.OrderH.OrderID, '+@OrderQty+ ' ,POS.OrderH.OrderDate,POS.SKU.Color,POS.SKU.Size,POS.SKU.Spec,POS.SKU.Memo
from POS.SKU,POS.OrderH '+@where+ ' Order By SKU.SKU,OrderH.OrderDate DESC '
exec (@sql)
有劳各位啦~~~~~
[解决办法]
--好像错误不少
--建议不要用动态语句
CREATE proc [dbo].[GetSKUSalesDetailsBySKU]
@SKU varchar(10), --建议改成varchar，不改也许也可以
@SKUName nvarchar(50),
@MinOrderDate datetime,
@MaxOrderDate datetime
as
declare @where varchar(500)
set @where= ' Where POS.SKU.SKU=POS.OrderD.SKU AND POS.OrderD.OrderID=POS.OrderH.OrderID AND POS.OrderH.OrderDate between ' ' '+ CONVERT(varchar(50), @MinOrderDate)+ ' ' ' and ' ' '+CONVERT(varchar(50),@MaxOrderDate)+ ' ' ' '
--以下两句改了小地方
if @SKU <> ' ' begin set @where=@where+ ' and POS.SKU.SKU= ' ' '+@SKU+ ' ' ' ' end
if @SKUName <> ' ' begin set @where=@where+ ' and POS.SKU.SKUName= ' ' '+@SKUName+ ' ' ' ' end
--这句应该改动比较大，而且不知道OrderQty的计算是否有问题
declare @OrderQty varchar(500)
set @OrderQty= '(select sum(POS.SKU.OrderQty) from POS.SKU,POS.OrderD,POS.OrderH '+@where+ ') as OrderQty '
--这句也需要修改
declare @sql varchar(1000)
set @sql= 'select POS.SKU.SKU,POS.SKU.SKUName,POS.OrderH.Type,POS.OrderH.OrderID, '+@OrderQty+ ' ,POS.OrderH.OrderDate,POS.SKU.Color,POS.SKU.Size,POS.SKU.Spec,POS.SKU.Memo
from POS.SKU,POS.OrderH,POS.OrderD '+@where+ ' Order By SKU.SKU,OrderH.OrderDate DESC '
exec (@sql)
