如何在不需要嵌套IF的情况下通过参数进行组合 [英] How To do a Combination by Paramters Without Needing a Nested IF
本文介绍了如何在不需要嵌套IF的情况下通过参数进行组合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在SQL Server中创建了一个存储过程，从具有多个可选参数的多个表中进行选择并在报表查看器中的Rdlc报表中检索此数据
这是程序代码
ALTER PROC [dbo]。[SP_SalesReport]
@ Sheet Nvarchar （ 50 ）= NULL ，
@Plot Nvarchar （ 50 ）= NULL ，
@Category Nvarchar （ 50 ）= NULL ，
@ Type Nvarchar （ 50 ）= NULL ，
@ Variety Nvarchar （ 50 ）= NULL ，
@ Sector Nvarchar （ 50 ）= NULL ，
@ Owner Nvarchar （ 50 ）= NULL ，
@ Company Nvarchar （ 50 ）= NULL ，
@国家 varchar （ 2 ）= NULL ，
@ Statues Nvarchar （ 50 ）= NULL ，
@ Activity Nvarchar （ 7 ）= NULL ，
@ UnitPrice 十进制（ 18 ， 3 ）= NULL ，
@ Week INT = NULL ，
@ Date DATE = NULL
AS
BEGIN
SELECT
Sheets.SheetNo AS ' SheetNumber'，
Plots.PlotName < span class =code-k eyword> AS ' Plot'，
Plots.GrossArea AS ' GrossArea'，
RootStocks。 RootStockName AS ' RootStock'，
Categories.CategoryName AS ' Category'，
Types.TypeName AS ' 类型'，
Varieties.VarietyName AS ' Variety，
Sectors.SectorName AS ' Sector'，
Owners.OwnerName AS ' 所有者'，
CONVERT （ Varchar （ 3 ），DATENAME（WEEKDAY，Sheets。 Date ））+ ' - ' +
CONVERT （ varchar （ 30 ），
+ DATENAME（DAY） ，表格。日期）+ ' '
+ CONVERT （ Varchar （ 3 ），DATENAME（MONTH，Sheets。 Date ））+ ' '
+ DATENAME（年份，表格。日期）） AS ' 日期'，
DATEPART（周刊，表格。日期）< span class =code-keyword> AS ' 周'，
CONVERT （ VARCHAR （ 5 ），表格。时间， 108 ） AS ' 时间'，
Cars.CarNo AS ' Car'，
Cars.Driver AS ' Driver'，
Companies.CompanyName AS ' 公司'，
（ CASE WHEN Companies.CompanyActivity = 0 那么 ' Local'
ELSE ' 导出'
END ） AS 活动，
Companies.CountryRegionCode AS ' Country'，
Representatives.RepresentativeName AS ' 代表'，
Orders.Boxes AS ' Boxes'，
Orders.QuantityKG AS < span class =code-string>' Quantity'，
CONVERT （ NVARCHAR （ 18 ),Orders.UnitPrice）+ ' ' + Orders.CurrencyCode AS ' UnitPrice'，
CONVERT （ Nvarchar （ 50 ），（ CONVERT （ DECIMAL （ 18 ， 3 ），Orders.UnitPrice * Orders.QuantityKG）））+ ' ' + Orders.CurrencyCode AS ' TotalPrice'，
CONVERT （ DECIMAL （ 18 ， 3 ），Orders.QuantityKG / Orders.Boxes） AS < span class =code-string>' BoxAverage'，
ExportType.TypeName AS ' ExportType'，
（ CASE WHEN Orders.Statues = 0 那么 ' 付费'
ELSE ' 待定'
END ） AS ' 雕像，
Orders.PendingPeriod AS ' PendingPeriod'，
CONVERT （ DECIMAL （ 18 ， 3 ），Orders.QuantityKG / Plots.GrossArea） AS < span class =code-string>' AverageQuantityGArea'，
CONVERT （ DECIMAL （ 18 ， 3 ）， Orders.QuantityKG /（ SELECT NetArea FROM PlotDescriptionByYear WHERE PlotID = Orders.PlotID AND Season = YEAR（表格。日期））） AS ' AverageQuantityNArea'，
CONVERT （ DECIMAL （ 18 ， 3 ），Orders.QuantityKG /（ SELECT PlotDescriptionByYear.NumberOfPlants FROM PlotDescriptionByYear WHERE PlotDescriptionByYear.Season = YEAR （表格。日期） AND PlotID = Orders.PlotID）） AS ' AverageQuantityPlantsNumber'，
CONVERT （ NVARCHAR （ 50 ）， CONVERT （ DECIMAL （ 18 ， 3 ）， CONVERT （ DECIMAL （ 18 ， 3 ），Orders.UnitPrice * Orders.QuantityKG）/ CONVERT（ DECIMAL （ 18 ， 3 ），（Plots.GrossArea））））+ ' ' + Orders.CurrencyCode AS ' AveragePriceGArea'，
CONVERT （ NVARCHAR （ 18 ）， CONVERT （ DECIMAL （ 18 ， 3 ）， CONVERT （ DECIMAL （ 18 ， 3 ），Orders.UnitPrice * Orders.QuantityKG）/ CONVERT（ DECIMAL （ 18 ， 3 ），（ SELECT NetArea FROM PlotDescriptionByYear WHERE Season = YEAR（表格。日期） AND PlotID = Orders.PlotID））））+ ' ' + Orders.CurrencyCode AS ' AveragePriceNArea'，
CONVERT （ NVARCHAR （ 18 ）， CONVERT （ DECIMAL （ 18 ， 3 ），Orders.UnitPrice * Orders.QuantityKG / （ SELECT PlotDescriptionByYear.NumberofPlants FROM PlotDescriptionByYear WHERE 季节= YEAR（表格。日期） AND PlotDescriptionByYear.PlotID = Orders.PlotID）））+ ' ' + Orders.CurrencyCode AS ' AveragePricePlantsNumber'
FROM
Sheets
INNER JOIN
订单 ON Sheets.SheetID = Orders.SheetID
INNER JOIN
Plots ON Orders.PlotID = Plots.PlotID
INNER JOIN
PlotDescriptionByYear ON PlotDescriptionByYear.PlotID = Plots.PlotID
INNER JOIN
公司 ON Orders.CompanyID = Companies.CompanyID
INNER JOIN
代表 ON Orders.RepresentativeID =代表.RepresentativeID
INNER JOIN
ExportType ON Orders.ExportTypeID = ExportType.TypeID
INNER JOIN
RootStocks ON PlotDescriptionByYear.RootStockID = RootStocks.RootStockID
INNER JOIN
类别 ON PlotDescriptionByYear.CategoryID = Categories.CategoryID
INNER JOIN
类型 ON PlotDescriptionByYear.TypeID = Types.TypeID
INNER JOIN
品种 ON PlotDescriptionByYear.VarietyID = Varieties.VarietyID
INNER JOIN
Cars ON Orders.CarID = Cars.CarID
INNER JOIN
部门 ON Plots.SectorID = Sectors.SectorID
INNER JOIN
所有者 ON Plots.OwnerID = Owners.OwnerID
WHERE PlotDescriptionByYear.Season = YEAR（Sheets 。日期）
AND （ @ She et IS NULL 或 （Sheets.SheetNo = @ Sheet ）） AND
（ @Plot IS NULL 或（Plots.PlotName = @ Plot ）） AND
（ @ Category IS NULL OR （Categories.CategoryName = @ Category ）） AND
（ @ Type IS NULL OR （Types.TypeName = @ Type ）） AND
（ @ Variety IS NULL 或（Varieties.VarietyName = @ Variety ）） AND
（ @ Sector IS NULL OR （Sectors.SectorName = @扇区）） AND
（ @ Owner IS NULL 或（Owners.OwnerName = @ Owner ）） AND
（ @ Company IS NULL OR （Companies.CompanyName = @ Company ）） AND
（ @ Country IS NULL OR （Companies.CountryRegionCode = @ Country ）） AND
（ @ Statues IS NULL 或（（ CASE WHEN 订单。 Statues = 0 那么 ' 付费' ELSE ' 待定 END ）= @ Statues ）） AND
（ @ Activity IS NULL 或（（ CASE WHEN Companies.CompanyActivity = 0 THEN ' Local' ELSE ' 导出' END ）= @ Activity ）） AND
（ @UnitPrice IS NULL OR （Orders.UnitPrice = @ UnitPrice ）） AND
（ @ Week IS NULL 或（（DATEPART（wk，Sheets。 Date ））= @ Week ）） AND
（ @ Date IS NULL 或（表格。日期 = @ Date ））
END
和用户希望能够搜索1个参数，2个参数和3个参数
所以我创建了一个win表单，其中包含报表查看器和3个组合框，每个组合框都有proc的参数，因此当用户选择参数时，它会发送选择参数的值，而其他参数值将为NULL问题 我试图用NESTED这样做我发现由于参数的组合我会发出大量的IF语句所以我要求找到任何其他简短的方法来做这个吗？！
解决方案
我认为没有必要在条件下放置嵌套。在存储过程中使用一些技巧，以便如果任何参数为null ...可以忽略。
这里讨论并解决相同的问题：
http://stackoverflow.com/questions/2329674/sql-conditional-where-clause [ ^ ]
另一个有用的链接可能是：
http://dba.stackexchange.com/questions/74461/stored-procedure-null-parameter-within-where-clause [ ^ ]
希望那些会有所帮助。感谢。
I Created a Stored Procdure In SQL Server To Do a Select From Multiple Table With Multiple Optional Parameters and Retrieve This Data in Rdlc Report in Report Viewer
Here Is The Code Of Proce
ALTER PROC [dbo].[SP_SalesReport]
@Sheet Nvarchar(50) = NULL,
@Plot Nvarchar(50) = NULL,
@Category Nvarchar(50) = NULL,
@Type Nvarchar(50) = NULL,
@Variety Nvarchar(50) = NULL,
@Sector Nvarchar(50) = NULL,
@Owner Nvarchar(50) = NULL,
@Company Nvarchar(50) = NULL,
@Country varchar(2) = NULL,
@Statues Nvarchar(50) = NULL,
@Activity Nvarchar(7) = NULL,
@UnitPrice Decimal(18,3) = NULL,
@Week INT = NULL,
@Date DATE = NULL
AS
BEGIN
SELECT
Sheets.SheetNo AS 'SheetNumber',
Plots.PlotName AS 'Plot',
Plots.GrossArea AS 'GrossArea',
RootStocks.RootStockName AS 'RootStock',
Categories.CategoryName AS 'Category',
Types.TypeName AS 'Type',
Varieties.VarietyName AS 'Variety',
Sectors.SectorName AS 'Sector',
Owners.OwnerName AS 'Owner',
CONVERT(Varchar(3),DATENAME(WEEKDAY, Sheets.Date)) + ' - '+
CONVERT (varchar(30),
+ DATENAME(DAY, Sheets.Date) + ' '
+ CONVERT(Varchar(3),DATENAME(MONTH, Sheets.Date)) + ' '
+ DATENAME(YEAR, Sheets.Date)) AS 'Date',
DATEPART( wk, Sheets.Date) AS 'Week',
CONVERT(VARCHAR(5), Sheets.Time,108) AS 'Time',
Cars.CarNo AS 'Car',
Cars.Driver AS 'Driver',
Companies.CompanyName AS 'Company',
(CASE WHEN Companies.CompanyActivity = 0 THEN 'Local'
ELSE 'Export'
END) AS Activity,
Companies.CountryRegionCode AS 'Country',
Representatives.RepresentativeName AS 'Representative',
Orders.Boxes AS 'Boxes',
Orders.QuantityKG AS 'Quantity',
CONVERT(NVARCHAR(18),Orders.UnitPrice) + ' ' + Orders.CurrencyCode AS 'UnitPrice',
CONVERT(Nvarchar(50),(CONVERT(DECIMAL(18,3), Orders.UnitPrice * Orders.QuantityKG)))+ ' ' + Orders.CurrencyCode AS 'TotalPrice',
CONVERT(DECIMAL(18,3), Orders.QuantityKG / Orders.Boxes) AS 'BoxAverage',
ExportType.TypeName AS 'ExportType',
(CASE WHEN Orders.Statues = 0 THEN 'Paid'
ELSE 'Pending'
END) AS 'Statues',
Orders.PendingPeriod AS 'PendingPeriod',
CONVERT(DECIMAL(18,3),Orders.QuantityKG/Plots.GrossArea) AS 'AverageQuantityGArea',
CONVERT(DECIMAL(18,3), Orders.QuantityKG/(SELECT NetArea FROM PlotDescriptionByYear WHERE PlotID = Orders.PlotID AND Season = YEAR(Sheets.Date)) ) AS 'AverageQuantityNArea',
CONVERT(DECIMAL(18,3), Orders.QuantityKG / (SELECT PlotDescriptionByYear.NumberOfPlants FROM PlotDescriptionByYear WHERE PlotDescriptionByYear.Season = YEAR(Sheets.Date) AND PlotID = Orders.PlotID)) AS 'AverageQuantityPlantsNumber',
CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),CONVERT(DECIMAL(18,3), Orders.UnitPrice * Orders.QuantityKG)/CONVERT(DECIMAL(18,3),(Plots.GrossArea)))) + ' ' + Orders.CurrencyCode AS 'AveragePriceGArea',
CONVERT(NVARCHAR(18),CONVERT(DECIMAL(18,3),CONVERT(DECIMAL(18,3), Orders.UnitPrice * Orders.QuantityKG)/CONVERT(DECIMAL(18,3),(SELECT NetArea FROM PlotDescriptionByYear WHERE Season = YEAR(Sheets.Date) AND PlotID = Orders.PlotID)))) + ' ' + Orders.CurrencyCode AS 'AveragePriceNArea',
CONVERT(NVARCHAR(18),CONVERT(DECIMAL(18,3), Orders.UnitPrice * Orders.QuantityKG / (SELECT PlotDescriptionByYear.NumberofPlants FROM PlotDescriptionByYear WHERE Season = YEAR(Sheets.Date) AND PlotDescriptionByYear.PlotID = Orders.PlotID))) + ' ' + Orders.CurrencyCode AS 'AveragePricePlantsNumber'
FROM
Sheets
INNER JOIN
Orders ON Sheets.SheetID = Orders.SheetID
INNER JOIN
Plots ON Orders.PlotID = Plots.PlotID
INNER JOIN
PlotDescriptionByYear ON PlotDescriptionByYear.PlotID = Plots.PlotID
INNER JOIN
Companies ON Orders.CompanyID = Companies.CompanyID
INNER JOIN
Representatives ON Orders.RepresentativeID = Representatives.RepresentativeID
INNER JOIN
ExportType ON Orders.ExportTypeID = ExportType.TypeID
INNER JOIN
RootStocks ON PlotDescriptionByYear.RootStockID = RootStocks.RootStockID
INNER JOIN
Categories ON PlotDescriptionByYear.CategoryID = Categories.CategoryID
INNER JOIN
Types ON PlotDescriptionByYear.TypeID = Types.TypeID
INNER JOIN
Varieties ON PlotDescriptionByYear.VarietyID = Varieties.VarietyID
INNER JOIN
Cars ON Orders.CarID = Cars.CarID
INNER JOIN
Sectors ON Plots.SectorID = Sectors.SectorID
INNER JOIN
Owners ON Plots.OwnerID = Owners.OwnerID
WHERE PlotDescriptionByYear.Season = YEAR(Sheets.Date)
AND (@Sheet IS NULL OR (Sheets.SheetNo = @Sheet)) AND
(@Plot IS NULL OR (Plots.PlotName = @Plot)) AND
(@Category IS NULL OR (Categories.CategoryName = @Category)) AND
(@Type IS NULL OR (Types.TypeName = @Type)) AND
(@Variety IS NULL OR (Varieties.VarietyName = @Variety)) AND
(@Sector IS NULL OR (Sectors.SectorName = @Sector)) AND
(@Owner IS NULL OR (Owners.OwnerName = @Owner)) AND
(@Company IS NULL OR (Companies.CompanyName = @Company)) AND
(@Country IS NULL OR (Companies.CountryRegionCode = @Country)) AND
(@Statues IS NULL OR ((CASE WHEN Orders.Statues = 0 THEN 'Paid' ELSE 'Pending' END) = @Statues)) AND
(@Activity IS NULL OR ((CASE WHEN Companies.CompanyActivity = 0 THEN 'Local' ELSE 'Export' END) = @Activity)) AND
(@UnitPrice IS NULL OR (Orders.UnitPrice = @UnitPrice)) AND
(@Week IS NULL OR ((DATEPART( wk, Sheets.Date)) = @Week)) AND
(@Date IS NULL OR (Sheets.Date = @Date))
END
and The User Want To be able to search with 1 param , 2 params and 3 params
so i created a win form that have the report viewer and putted 3 combo boxes each one of them have the parameters of the proc so when the user choose paramters it send the values of choosen parameters and the other parameter value will be NULL so the problem is i tried to do this with NESTED IF i found that i will make a huge amount of IF statements due to the Combinations of the parameters so i am asking about finding any other short way to do this ?!
解决方案
I think there no need to put nested if conditions. Use some trick, in Stored Procedure, so that if any parameter is null...that could be ignored.
Same issue is discussed and resolved here:
http://stackoverflow.com/questions/2329674/sql-conditional-where-clause[^]
Another useful link may be:
http://dba.stackexchange.com/questions/74461/stored-procedure-null-parameter-within-where-clause[^]
Hope those will help. Thanks.
这篇关于如何在不需要嵌套IF的情况下通过参数进行组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
