SQL 存储过程参数解决方案

今天做这个一个问题，需要动态使用Sql语句，原来的解决方案是在程序中是用

if () {} else{}语句来解决

后来因为数据问题，没办法只有使用存储过程。

源代码是这样的

--exec sp_SupplyIntoStock '2007-03-01','2009-05-31','','',''CREATE PROCEDURE [dbo].[sp_SupplyIntoStock] @StartDate VARCHAR(50), --开始时间 @EndDate VARCHAR(50), --结束时间 @MFK_Big VARCHAR(50),--商品大类 @MFK_Small VARCHAR(50),--商品小类 @S_Name VARCHAR(50) --供应商名称 AS BEGIN create table #Temp1 ( S_Name VARCHAR(50), MFK_Big VARCHAR(50), MFK_Small VARCHAR(50), IntoMoney decimal(18,4) ) create table #Temp2 ( S_Name VARCHAR(50), MFK_Big VARCHAR(50), MFK_Small VARCHAR(50), UG_IntoMoney decimal(18,4) ) --供应商入库信息INSERT INTO #Temp1(S_Name,MFK_Big,MFK_Small,IntoMoney)SELECT dbo.Supply.Cdesc AS S_Name, dbo.MerchandiseBig.CDesc AS MFK_Big, dbo.MerchandiseSmall.CDesc AS MFK_Small, CONVERT(numeric(20, 2), SUM(dbo.Stock_IntoStockList.Into_Num * dbo.Stock_IntoStockList.Into_Price)) AS IntoMoneyFROM dbo.Stock_IntoStock INNER JOIN dbo.Stock_IntoStockList ON dbo.Stock_IntoStock.Into_Code = dbo.Stock_IntoStockList.Into_Code INNER JOIN dbo.MerchandiseBig INNER JOIN dbo.Merchandise ON dbo.MerchandiseBig.MBCode = dbo.Merchandise.MFK_Big ON dbo.Stock_IntoStockList.M_Code = dbo.Merchandise.CreateCode INNER JOIN dbo.MerchandiseSmall ON dbo.Merchandise.MFK_Small = dbo.MerchandiseSmall.MSCode INNER JOIN dbo.Supply ON dbo.Stock_IntoStock.Supply_Code = dbo.Supply.SCodeWHERE (dbo.Stock_IntoStockList.Into_Status = 'C') and dbo.Stock_IntoStockList.Update_Date >= @StartDate and dbo.Stock_IntoStockList.Update_Date <= @EndDate and dbo.MerchandiseBig.CDesc = @MFK_Big and dbo.MerchandiseSmall.CDesc = @MFK_Small and dbo.Supply.Cdesc = @S_NameGROUP BY dbo.Supply.Cdesc, dbo.MerchandiseBig.CDesc, dbo.MerchandiseSmall.CDescORDER BY dbo.Supply.Cdesc--供应商退货信息INSERT INTO #Temp2(S_Name,MFK_Big,MFK_Small,UG_IntoMoney)SELECT dbo.Supply.Cdesc AS S_Name, dbo.MerchandiseBig.CDesc AS MFK_Big,dbo.MerchandiseSmall.CDesc AS MFK_Small, CONVERT(numeric(20, 2), SUM(ISNULL(dbo.Stock_UntreadGoodsList.UG_Num * dbo.Stock_UntreadGoodsList.UG_Price, 0))) AS UG_IntoMoney FROM dbo.Supply INNER JOIN dbo.Stock_UntreadGoodsList INNER JOIN dbo.Stock_UntreadGoods ON dbo.Stock_UntreadGoodsList.UG_Code = dbo.Stock_UntreadGoods.UG_Code ON dbo.Supply.SCode = dbo.Stock_UntreadGoods.Supply_Code RIGHT OUTER JOIN dbo.MerchandiseSmall INNER JOIN dbo.MerchandiseBig INNER JOIN dbo.Merchandise ON dbo.MerchandiseBig.MBCode = dbo.Merchandise.MFK_Big ON dbo.MerchandiseSmall.MSCode = dbo.Merchandise.MFK_Small ON dbo.Stock_UntreadGoodsList.M_Code = dbo.Merchandise.CreateCodeWHERE (dbo.Stock_UntreadGoodsList.UG_Status = 'C') and dbo.Stock_UntreadGoodsList.Update_Date >= @StartDate and dbo.Stock_UntreadGoodsList.Update_Date <= @EndDate and dbo.MerchandiseBig.CDesc = @MFK_Big and dbo.MerchandiseSmall.CDesc = @MFK_Small and dbo.Supply.Cdesc = @S_NameGROUP BY dbo.Supply.Cdesc, dbo.MerchandiseBig.CDesc, dbo.MerchandiseSmall.CDescORDER BY dbo.Supply.Cdescselect T1.S_Name,T1.MFK_Big, T1.MFK_Small, CONVERT(NUMERIC(20,2),T1.IntoMoney - isnull (T2.UG_IntoMoney,0)) AS IntoMoneyfrom #Temp1 AS T1 left outer JOIN #Temp2 AS T2 ON T2.S_Name = T1.S_NameENDGO

这个存储过程是对的，但是里面有5个参数，但是在程序中除了时间外的另外三个参数都有可能为空，而且在为空的情况下需要能够检索查询出所有信息！

一时间真有点为难！

好在天无绝人之路，方法竟然找到了：

同样的Sql语句，做了小小的修改

 

--exec sp_SupplyIntoStock '2007-03-01','2009-05-31','','',''
CREATE  PROCEDURE [dbo].[sp_SupplyIntoStock]
    @StartDate VARCHAR(50), --开始时间
    @EndDate VARCHAR(50), --结束时间
    @MFK_Big VARCHAR(50),--商品大类
    @MFK_Small VARCHAR(50),--商品小类
    @S_Name VARCHAR(50) --供应商名称
 AS
 BEGIN
 create table #Temp1
 (
    S_Name VARCHAR(50),
    MFK_Big  VARCHAR(50),
    MFK_Small VARCHAR(50),
    IntoMoney   decimal(18,4)
 )
 create table #Temp2
 (
    S_Name VARCHAR(50),
    MFK_Big  VARCHAR(50),
    MFK_Small VARCHAR(50),
    UG_IntoMoney decimal(18,4)
  )

--供应商入库信息
INSERT INTO #Temp1(S_Name,MFK_Big,MFK_Small,IntoMoney)
SELECT  dbo.Supply.Cdesc AS S_Name, dbo.MerchandiseBig.CDesc AS MFK_Big, dbo.MerchandiseSmall.CDesc AS MFK_Small,
        CONVERT(numeric(20, 2), SUM(dbo.Stock_IntoStockList.Into_Num * dbo.Stock_IntoStockList.Into_Price)) AS IntoMoney
FROM    dbo.Stock_IntoStock INNER JOIN
        dbo.Stock_IntoStockList ON dbo.Stock_IntoStock.Into_Code = dbo.Stock_IntoStockList.Into_Code INNER JOIN
        dbo.MerchandiseBig INNER JOIN
        dbo.Merchandise ON dbo.MerchandiseBig.MBCode = dbo.Merchandise.MFK_Big ON
        dbo.Stock_IntoStockList.M_Code = dbo.Merchandise.CreateCode INNER JOIN
        dbo.MerchandiseSmall ON dbo.Merchandise.MFK_Small = dbo.MerchandiseSmall.MSCode INNER JOIN
        dbo.Supply ON dbo.Stock_IntoStock.Supply_Code = dbo.Supply.SCode
WHERE   (dbo.Stock_IntoStockList.Into_Status = 'C')
       and  dbo.Stock_IntoStockList.Update_Date >= @StartDate
       and  dbo.Stock_IntoStockList.Update_Date <= @EndDate
       and  (dbo.MerchandiseBig.CDesc = @MFK_Big or @MFK_Big ='')
       and  (dbo.MerchandiseSmall.CDesc = @MFK_Small or @MFK_Small='' )
       and  (dbo.Supply.Cdesc = @S_Name or @S_Name='')

GROUP BY dbo.Supply.Cdesc, dbo.MerchandiseBig.CDesc, dbo.MerchandiseSmall.CDesc
ORDER BY dbo.Supply.Cdesc
--供应商退货信息
INSERT INTO #Temp2(S_Name,MFK_Big,MFK_Small,UG_IntoMoney)
SELECT dbo.Supply.Cdesc AS S_Name, dbo.MerchandiseBig.CDesc AS MFK_Big,dbo.MerchandiseSmall.CDesc AS MFK_Small,
       CONVERT(numeric(20, 2), SUM(ISNULL(dbo.Stock_UntreadGoodsList.UG_Num * dbo.Stock_UntreadGoodsList.UG_Price, 0))) AS UG_IntoMoney     
FROM   dbo.Supply INNER JOIN
       dbo.Stock_UntreadGoodsList INNER JOIN
       dbo.Stock_UntreadGoods ON dbo.Stock_UntreadGoodsList.UG_Code = dbo.Stock_UntreadGoods.UG_Code ON
       dbo.Supply.SCode = dbo.Stock_UntreadGoods.Supply_Code RIGHT OUTER JOIN
       dbo.MerchandiseSmall INNER JOIN
       dbo.MerchandiseBig INNER JOIN
       dbo.Merchandise ON dbo.MerchandiseBig.MBCode = dbo.Merchandise.MFK_Big ON
       dbo.MerchandiseSmall.MSCode = dbo.Merchandise.MFK_Small ON dbo.Stock_UntreadGoodsList.M_Code = dbo.Merchandise.CreateCode
WHERE  (dbo.Stock_UntreadGoodsList.UG_Status = 'C')
       and  dbo.Stock_UntreadGoodsList.Update_Date >= @StartDate
       and  dbo.Stock_UntreadGoodsList.Update_Date <= @EndDate
       and  (dbo.MerchandiseBig.CDesc = @MFK_Big or @MFK_Big ='')
       and  (dbo.MerchandiseSmall.CDesc = @MFK_Small or @MFK_Small='' )
       and  (dbo.Supply.Cdesc = @S_Name or @S_Name='')

GROUP BY dbo.Supply.Cdesc, dbo.MerchandiseBig.CDesc, dbo.MerchandiseSmall.CDesc
ORDER BY dbo.Supply.Cdesc

select T1.S_Name,T1.MFK_Big, T1.MFK_Small,
 CONVERT(NUMERIC(20,2),T1.IntoMoney - isnull (T2.UG_IntoMoney,0)) AS IntoMoney
from #Temp1 AS T1
     left outer JOIN #Temp2 AS T2 ON T2.S_Name = T1.S_Name

END
GO

 

问题解决了！