How To Execute This Query When Any One Of The Parameter Is Null Or Zero Because I Am Using And Operation So.

Question

0.00/5 (No votes)

See more:

Step 1: I have 5 drop down list and when i select all the dropdown list i will get the five values and these five values pass to a method to execute the query.

C#

 public JsonResult GetCategorys(int rgLevelId, int PGID, int PItemID, int lbuId, int subCountryId)
{
  SELECT sum(T.T_TS )as Target,sum(T.A_TS) as Actual,T.latitude as Latitude
FROM OCF.TempData T
WHERE T.PG_Id=195 AND T.YearOfAss=2016 AND T.PGItem_Id=1 AND T.OL_Id=1002308 AND T.Country_Id=58 AND T.InnerCountry_Id=59
GROUP BY T.latitude
}

Its working fine.

Question:
---------
I need to execute this query when any of the parameter is null or 0 because when not selected value from the dropdown list.
HOW TO execute this query when any one of the parameter is null or zero because i am using AND operation so.

Thanks
Basheer

Posted 19-Jan-16 19:45pm

Basheer Belgod

Add a Solution

Comments

Sri Nivas (Vasu) 20-Jan-16 2:13am

Do you want to ignore the condition if the value is null or zero?

3 solutions

Solution 1

You can make use of query that looks something like this:

SQL

select column1,column2 from table where
(column1 = @column1 or @column1 is null) and
(column2 = @column2 or @column2 is null)

Posted 19-Jan-16 20:00pm

dan!sh

Solution 2

As per your scenario, you need to use Dynamic SQL query. Implement your logic and generate the SQL query - then execute it.

Building Dynamic SQL In a Stored Procedure[^]

Posted 19-Jan-16 20:03pm

Manas_Kumar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sri Nivas (Vasu) · Accepted Answer · 2016-01-19T20:18:00

Solution 3

Below should work for you

C#

public JsonResult GetCategorys(int rgLevelId, int PGID, int PItemID, int lbuId, int subCountryId)
{
  SELECT sum(T.T_TS )as Target,sum(T.A_TS) as Actual,T.latitude as Latitude
    FROM OCF.TempData T
    WHERE 
       ( T.PG_Id=PGID OR PGID = 0) 
       AND T.YearOfAss=2016 
       AND (T.PGItem_Id=1  OR PItemID = 0)
       AND ( T.OL_Id= rgLevelId OR rgLevelId = 0)
       AND T.Country_Id=58 
       AND ( T.InnerCountry_Id=subCountryId OR subCountryId = 0)
   GROUP BY T.latitude
}

Posted 19-Jan-16 20:18pm

Sri Nivas (Vasu)

Comments

Basheer Belgod 20-Jan-16 4:51am

I am not getting out put which i want
CREATE TABLE [ocf].[TempData] (
[PK_Id] INT NOT NULL,
[PG_Id] INT NOT NULL,
[PGItem_Id] INT NOT NULL,
[PGSkill_Id] INT NOT NULL,
[OL_Id] INT NOT NULL,
[Country_Id] INT NOT NULL,
[InnerCountry_Id] INT NOT NULL,
[YearOfAss] INT NOT NULL,
[T_TS] INT NOT NULL,
[T_E1] INT NOT NULL,
[T_C1] INT NOT NULL,
[A_TS] INT NOT NULL,
[A_E1] INT NOT NULL,
[A_C1] INT NOT NULL,
[latitude] DECIMAL (12, 9) NULL,
[longitude] DECIMAL (12, 9) NULL,
[name] VARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([PK_Id] ASC)
);

INSERT INTO [ocf].[TempData] ([PK_Id], [PG_Id], [PGItem_Id], [PGSkill_Id], [OL_Id], [Country_Id], [InnerCountry_Id], [YearOfAss], [T_TS], [T_E1], [T_C1], [A_TS], [A_E1], [A_C1], [latitude], [longitude], [name]) VALUES (1, 195, 1, 223, 1002308, 58, 59, 2013, 10, 10, 10, 10, 10, 10, CAST(57.381642000 AS Decimal(12, 9)), CAST(-81.563760000 AS Decimal(12, 9)), N'Disney World')
INSERT INTO [ocf].[TempData] ([PK_Id], [PG_Id], [PGItem_Id], [PGSkill_Id], [OL_Id], [Country_Id], [InnerCountry_Id], [YearOfAss], [T_TS], [T_E1], [T_C1], [A_TS], [A_E1], [A_C1], [latitude], [longitude], [name]) VALUES (2, 195, 1, 223, 1002308, 58, 59, 2014, 11, 11, 11, 11, 11, 11, CAST(34.474907000 AS Decimal(12, 9)), CAST(-81.466316000 AS Decimal(12, 9)), N'Universal Studios')
INSERT INTO [ocf].[TempData] ([PK_Id], [PG_Id], [PGItem_Id], [PGSkill_Id], [OL_Id], [Country_Id], [InnerCountry_Id], [YearOfAss], [T_TS], [T_E1], [T_C1], [A_TS], [A_E1], [A_C1], [latitude], [longitude], [name]) VALUES (3, 195, 1, 233, 1002308, 58, 59, 2016, 20, 10, 12, 30, 15, 22, CAST(57.381642000 AS Decimal(12, 9)), CAST(-81.460018000 AS Decimal(12, 9)), N'Sea World')
INSERT INTO [ocf].[TempData] ([PK_Id], [PG_Id], [PGItem_Id], [PGSkill_Id], [OL_Id], [Country_Id], [InnerCountry_Id], [YearOfAss], [T_TS], [T_E1], [T_C1], [A_TS], [A_E1], [A_C1], [latitude], [longitude], [name]) VALUES (4, 195, 1, 233, 1002308, 58, 54, 2016, 30, 20, 10, 15, 13, 24, CAST(34.474907000 AS Decimal(12, 9)), CAST(-81.466316000 AS Decimal(12, 9)), N'ABB')
INSERT INTO [ocf].[TempData] ([PK_Id], [PG_Id], [PGItem_Id], [PGSkill_Id], [OL_Id], [Country_Id], [InnerCountry_Id], [YearOfAss], [T_TS], [T_E1], [T_C1], [A_TS], [A_E1], [A_C1], [latitude], [longitude], [name]) VALUES (5, 195, 1, 233, 1002308, 62, 63, 2016, 10, 10, 10, 10, 10, 10, CAST(12.474907000 AS Decimal(12, 9)), CAST(-81.466316000 AS Decimal(12, 9)), N'ABB')
INSERT INTO [ocf].[TempData] ([PK_Id], [PG_Id], [PGItem_Id], [PGSkill_Id], [OL_Id], [Country_Id], [InnerCountry_Id], [YearOfAss], [T_TS], [T_E1], [T_C1], [A_TS], [A_E1], [A_C1], [latitude], [longitude], [name]) VALUES (6, 195, 3, 233, 1002308, 58, 59, 2016, 50, 40, 20, 25, 20, 10, CAST(57.381642000 AS Decimal(12, 9)), CAST(-81.466316000 AS Decimal(12, 9)), N'ABB')
INSERT INTO [ocf].[TempData] ([PK_Id], [PG_Id], [PGItem_Id], [PGSkill_Id], [OL_Id], [Country_Id], [InnerCountry_Id], [YearOfAss], [T_TS], [T_E1], [T_C1], [A_TS], [A_E1], [A_C1], [latitude], [longitude], [name]) VALUES (7, 195, 3, 233, 1002308, 62, 63, 2016, 60, 50, 40, 30, 25, 20, CAST(12.474907000 AS Decimal(12, 9)), CAST(-81.466316000 AS Decimal(12, 9)), N'AAA')
INSERT INTO [ocf].[TempData] ([PK_Id], [PG_Id], [PGItem_Id], [PGSkill_Id], [OL_Id], [Country_Id], [InnerCountry_Id], [YearOfAss], [T_TS], [T_E1], [T_C1], [A_TS], [A_E1], [A_C1], [latitude], [longitude], [name]) VALUES (8, 195, 3, 233, 1002309, 71, 73, 2016, 80, 60, 40, 40, 30, 20, CAST(34.474907000 AS Decimal(12, 9)), CAST(-81.466316000 AS Decimal(12, 9)), N'sss')
INSERT INTO [ocf].[TempData] ([PK_Id], [PG_Id], [PGItem_Id], [PGSkill_Id], [OL_Id], [Country_Id], [InnerCountry_Id],

Basheer Belgod 20-Jan-16 4:55am

Output required
SELECT sum(T.T_TS )as Target,sum(T.A_TS) as Actual,T.latitude as Latitude
FROM OCF.TempData T
WHERE T.PG_Id=195 AND T.YearOfAss=2016 AND T.PGItem_Id=1 AND T.OL_Id=1002308 AND T.Country_Id=58 AND T.InnerCountry_Id=59
GROUP BY T.latitude

Current output:
SELECT sum(T.T_TS )as Target,sum(T.A_TS) as Actual,T.latitude as Latitude
FROM OCF.TempData T
WHERE (T.PG_Id=0 or T.PG_Id is null or T.PG_Id='' or T.PG_Id=195 )
AND ( T.YearOfAss=0 or T.YearOfAss is null or T.YearOfAss='' or T.YearOfAss=2016)
AND ( T.PGItem_Id=0 or T.PGItem_Id is null or T.PGItem_Id='' or T.PGItem_Id=1)
AND ( T.OL_Id=0 or T.OL_Id is null or T.OL_Id='' or T.OL_Id=1002308)
AND ( T.Country_Id=0 or T.Country_Id is null or T.Country_Id='' or T.Country_Id=58)
GROUP BY T.latitude

Note: When I use or option between AND block, I am getting different values like current output need outputlike

Sri Nivas (Vasu) 20-Jan-16 5:11am

I think you have added conditions like below

T.InnerCountry_Id=subCountryId OR T.InnerCountry_Id.subCountryId = 0

but that should be like below

T.InnerCountry_Id=subCountryId OR subCountryId = 0 //(subCountryId is parameter)

Please check once