- 爱易网页
-
MSSQL教程
- 一个让人几近崩溃的存储过程 @@解决方法
日期:2014-05-18 浏览次数:20471 次
一个让人几近崩溃的存储过程 @@
有这样三个表:
表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,