Click here to Skip to main content
15,891,607 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
SQL
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 ?!
Posted
Comments
BillWoodruff 27-Oct-15 9:49am    
"the items of combobox 2 is dependend on combobox 1 selection for example if combobox 1 have Item1,Item2,Item3,Item4 and user select Item1 From Combobox1 so Combobox 2 items will be Item2,Item3,Item4 and if user select Item2 From combobox2 so Combobox 3 Items Will Be Item3 and Item4 So that 's why i will need many nested if to make all possible options"

Why aren't you using one ComboBox with CheckBoxes ?
Muhammad Salah 27-Oct-15 10:36am    
because every combobox related to a textbox to pass the value of selected combobox item so that why i need to have 3 combobox and 3 textbox

1 solution

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.
 
Share this answer
 
v2
Comments
Muhammad Salah 26-Oct-15 21:58pm    
Firstly, Thanks For Your Reply

the problem not in the proc the problem the problem is in the passing values to the procs i have 3 combobox that contains the names of parameter if the user choose 2 items form 2 combobox so the proc receive a value of 2 parameters the problem in the Nested IF under the button of executing the proc
Example
IF ( combobox1.SelectedItem.ToString() == "Item 1" && Combobox1.SelectedItem == null && Combobox3.SelectedItem == null)
{
//Do Something
}
ELSE IF ( combobox1.SelectedItem.ToString() == "Item 1" && Combobox2.SelectedItem.ToString() == "Item 2" && Combobox3.SelectedItem == null)
{
//Do Something
}
ELSE IF ( combobox1.SelectedItem.ToString() == "Item 1" && Combobox2.SelectedItem.ToString() == "Item 2" && Combobox3.SelectedItem.ToString() == "Item 3")
{
//Do Something
}

And I Have 14 items So I Will Do a Huge Amout of Nested If In C# Form
Snesh Prajapati 26-Oct-15 22:23pm    
Thanks. ....I am not very clear about ..."//Do Something" .....I hope you are just calling Stored procedure there. If some value is null at C# code...send it as it is and described here:
http://stackoverflow.com/questions/13428563/send-null-value-into-stored-procedure

and
http://stackoverflow.com/questions/1207404/how-to-pass-a-null-variable-to-a-sql-stored-procedure-from-c-net-code

If you are not doing anything special if some value is null in C#,....just send null to DB and handle null there to ignore that parameter in query.
Muhammad Salah 26-Oct-15 22:27pm    
yes is calling stored procdure the problem is not in the proc i need to do all possible option for search to make it dynamic as possible as it can
Snesh Prajapati 26-Oct-15 22:44pm    
What you are are doing to make it dynamic ? ... and why the code is like:
IF ( combobox1.SelectedItem.ToString() == "Item 1" && Combobox1.SelectedItem == null && Combobox3.SelectedItem == null)
{
//Do Something
}
So what if in combobox1...user selects Item2 ? ....I am sure that combobox1 should be having more items ?
Muhammad Salah 26-Oct-15 22:47pm    
the items of combobox 2 is dependend on combobox 1 selection for example if combobox 1 have Item1,Item2,Item3,Item4 and user select Item1 From Combobox1 so Combobox 2 items will be Item2,Item3,Item4 and if user select Item2 From combobox2 so Combobox 3 Items Will Be Item3 and Item4 So that 's why i will need many nested if to make all possible options

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900