视图转换存储过程的一个例子
视图写得有些长，后来需求变为查询某时间段的收发存，条件必须在发货单的时候就要通过时间去筛选，这样不得不写存储过程了。
视图的分页与筛选，利用前面写的能用存储过程就能轻松完成，现在需要在存储过程里添加这些功能了。包括分页，筛选，与返回总记录数，大概写了一百多行吧。看到自己有了一点点进步，自己鼓励下。。
| /*************************************************************** |
| 名称: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) |
