视图转换存储过程的一个例子
视图写得有些长,后来需求变为查询某时间段的收发存,条件必须在发货单的时候就要通过时间去筛选,这样不得不写存储过程了。
视图的分页与筛选,利用前面写的能用存储过程就能轻松完成,现在需要在存储过程里添加这些功能了。包括分页,筛选,与返回总记录数,大概写了一百多行吧。看到自己有了一点点进步,自己鼓励下。。
/*************************************************************** |
名称:sp_StoreIOT |
作者:涂浩强 |
日期:2009-6-25 |
描述: 客户/仓库进销存 |
DECLARE @return_value int |
EXEC @return_value = [dbo].[sp_StoreIOT] |
@filterDe = N' where fSJoinDt between ''2008-9-10 0:00:00'' and ''2009-6-25 0:00:00'' ', |
@filterTo = N' where fSJoinDt<''2008-9-10 0:00:00'' ', |
@filterOut = N' where vDe.fSRId in (select fSRId from dbo.tbStoreRegion where fBCustId is not null) and vDe.fPId like ''%'' and vDe.fSBatch like ''%''', |
@start = 0, |
@limit = 50 |
SELECT 'Return Value' = @return_value |
select fSRId from dbo.tbStoreRegion where fBCustId is null |
***************************************************************/ |
alter proc sp_StoreIOT |
@filterDe varchar(200), |
@filterTo varchar(200), |
@filterOut varchar(200), |
@sortExp varchar(200) = NULL, |
@start int = 0, |
@limit int = 20 |
AS |
SET @sortExp ='vDe.'+RTRIM(LTRIM(@sortExp)) |
IF @sortExp IS NULL OR @sortExp = 'vDe.' or @sortExp='vDe.default' |
SET @sortExp = 'vDe.fPId' |
DECLARE @strStartRow varchar(50),@strEndRow varchar(50),@sql varchar(8000) |
IF @start < 0 |
SET @start = 0 |
SET @strStartRow = CAST((@start + 1) AS varchar(50)) |
SET @strEndRow = CAST((@start + @limit) AS varchar(50)) |
set @sql=' |
select |
ROW_NUMBER() OVER(Order By '+@sortExp+') as SysRowNum, |
vDe.fPId, |
vDe.fSBatch, |
vDe.fSRName, |
isnull(vTo.fSQuantityBefore,0) as fSQuantityBefore, |
vDe.fSPackQuantityIn, |
vDe.fSSingleQuantityIn, |
vDe.fSPackQuantityOut, |
vDe.fSSingleQuantityOut, |
vDe.fSQuantityNow, |
vDe.fSRId, |
vDe.fBUName |
from |
( |
SELECT |
s_in.fPId, |
s_in.fSBatch, |
ISNULL(ssr.fSRName, ''未指定'') as fSRName, |
isnull(s_in.fSPackQuantity,0) as fSPackQuantityIn, |
isnull(s_in.fSSingleQuantity,0) as fSSingleQuantityIn, |
isnull(s_out.fSPackQuantity,0) as fSPackQuantityOut, |
isnull(s_out.fSSingleQuantity,0) as fSSingleQuantityOut, |
(ISNULL((s_in.fSPackQuantity * vpp.fPQuantity + s_in.fSSingleQuantity), 0) - ISNULL((s_out.fSPackQuantity * vpp.fPQuantity + s_out.fSSingleQuantity), 0)) AS fSQuantityNow, |
s_in.fSRId, |
vpp.fBUName |
FROM |
( |
SELECT |
fPId, |
fSBatch, |
fSRId, |
sum(tbSInProdsDetails.fSPackQuantity) AS fSPackQuantity, |
sum(tbSInProdsDetails.fSSingleQuantity) AS fSSingleQuantity |
FROM tbSInProdsDetails left join tbSInProds on tbSInProdsDetails.fSIPId=tbSInProds.fSIPId |
'+@filterDe+'GROUP BY fPId, fSBatch, fSRId |
) s_in |
LEFT JOIN |
( |
SELECT |
fPId, |
fSBatch, |
fSRId, |
sum(opd.fSPackQuantity) AS fSPackQuantity, |
sum(opd.fSSingleQuantity) AS fSSingleQuantity |
FROM tbSOutProdsDetails opd left join tbSOutProds op on opd.fSOPId=op.fSOPId |
'+@filterDe+'GROUP BY fPId,fSBatch, fSRId |
) s_out |
ON s_in.fPId = s_out.fPId AND s_out.fSBatch = s_in.fSBatch AND s_out.fSRId = s_in.fSRId |
Left Join vProducts vpp On s_in.fPId = vpp.fPId |
Left Join tbStoreRegion ssr On s_in.fSRId = ssr.fSRId |
) vDe |
left join |
( |
SELECT |
s_in.fPId, |
s_in.fSBatch, |
s_in.fSRId, |
(ISNULL((s_in.fSPackQuantity * vpp.fPQuantity + s_in.fSSingleQuantity), 0) - ISNULL((s_out.fSPackQuantity * vpp.fPQuantity + s_out.fSSingleQuantity), 0)) AS fSQuantityBefore |
FROM |
( |
SELECT |
fPId, |
fSBatch, |
fSRId, |
sum(tbSInProdsDetails.fSPackQuantity) AS fSPackQuantity, |
sum(tbSInProdsDetails.fSSingleQuantity) AS fSSingleQuantity |
FROM tbSInProdsDetails left join tbSInProds on tbSInProdsDetails.fSIPId=tbSInProds.fSIPId |
'+@filterTo+' GROUP BY fPId, fSBatch, fSRId |
) s_in |
LEFT JOIN |
( |
SELECT |
fPId, |
fSBatch, |
fSRId, |
sum(opd.fSPackQuantity) AS fSPackQuantity, |
sum(opd.fSSingleQuantity) AS fSSingleQuantity |
FROM tbSOutProdsDetails opd left join tbSOutProds op on opd.fSOPId=op.fSOPId |
'+@filterTo+' GROUP BY fPId,fSBatch, fSRId |
) s_out |
ON s_in.fPId = s_out.fPId AND s_out.fSBatch = s_in.fSBatch AND s_out.fSRId = s_in.fSRId |
Left Join vProducts vpp On s_in.fPId = vpp.fPId |
)vTo on vDe.fPId=vTo.fPId and vDe.fSBatch=vTo.fSBatch and vDe.fSRId=vTo.fSRId ' |
+@filterOut; |
/*执行查询语句*/ |
SET @sql ='select * from ('+@sql+') v |
where SysRowNum between '+@strStartRow+' and '+@strEndRow |
+'; select count(SysRowNum) from ('+@sql+') v'; |
Exec (@sql) |
Print (@sql) |