本文介绍了如何在不需要嵌套IF的情况下通过参数进行组合的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我在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 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(周刊,表格。日期) 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 ' 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 那么 ' 付费' ELSE ' 待定' END ) AS ' 雕像, 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(表格。日期))) 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 ViewerHere Is The Code Of ProceALTER 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 = NULLASBEGINSELECTSheets.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'FROMSheetsINNER JOINOrders ON Sheets.SheetID = Orders.SheetIDINNER JOINPlots ON Orders.PlotID = Plots.PlotIDINNER JOINPlotDescriptionByYear ON PlotDescriptionByYear.PlotID = Plots.PlotIDINNER JOINCompanies ON Orders.CompanyID = Companies.CompanyIDINNER JOINRepresentatives ON Orders.RepresentativeID = Representatives.RepresentativeIDINNER JOINExportType ON Orders.ExportTypeID = ExportType.TypeIDINNER JOINRootStocks ON PlotDescriptionByYear.RootStockID = RootStocks.RootStockIDINNER JOINCategories ON PlotDescriptionByYear.CategoryID = Categories.CategoryIDINNER JOINTypes ON PlotDescriptionByYear.TypeID = Types.TypeIDINNER JOINVarieties ON PlotDescriptionByYear.VarietyID = Varieties.VarietyIDINNER JOINCars ON Orders.CarID = Cars.CarIDINNER JOINSectors ON Plots.SectorID = Sectors.SectorIDINNER JOINOwners ON Plots.OwnerID = Owners.OwnerIDWHERE 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))ENDand The User Want To be able to search with 1 param , 2 params and 3 paramsso 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的情况下通过参数进行组合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!