Static WHERE clause in SQLDatasource

Question

I need to execute the query according to the Businessunit values which is to be passed in runtime. If the businessunit is null the AND condition should not get executed and if it is not null i have to take the businessunit which is passed.

// SQL Query

SELECT nvrEmpCode, nvrEmpName 
FROM tblHRIMS_EmployeeDetail
WHERE (intCategoryID NOT IN (0, 1)) AND (nvrResigned = 'No') 
AND nvrBusinessunit=

All these must be done in a SQLDatasource and not as a StoredProcedure. How to achieve this?

Do you know you can also cast vote? (Not saying to cast for me. I am saying because you have 0 vote cast in your profile) — Himanshu
– Himanshu, Commented Sep 18, 2012 at 7:40

Himanshu · Accepted Answer · 2012-09-18 07:16:09Z

1

Your WHERE condition should be like this:

WHERE (intCategoryID NOT IN (0, 1)) AND (nvrResigned = 'No') 
AND (@businessunit IS NULL OR nvrBusinessunit=@businessunit)

answered Sep 18, 2012 at 7:16

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Talha Over a year ago

i thin your and 'Y. Ecarri' answers should be merge to get the complete answer

Himanshu Over a year ago

@Talha Not required as OP should know how to add parameters (As he/she has not asked it!)

Talha Over a year ago

ok, thanks both of you. but as approch wise, hims answer should be accepted.

Yván Ecarri · Accepted Answer · 2012-09-18 07:26:38Z

1

Assuming that you are using SQL Client:

SELECT nvrEmpCode, nvrEmpName 
FROM tblHRIMS_EmployeeDetail
WHERE (intCategoryID NOT IN (0, 1)) AND (nvrResigned = 'No') 
AND (nvrBusinessunit=@BU OR @BU is null)

Then you have to pass a value for the parameter:

command.Parameters.AddWithValue(@BU, yourValue)

edited Sep 18, 2012 at 7:26

answered Sep 18, 2012 at 7:16

Yván Ecarri

1,76019 silver badges42 bronze badges

Comments

Kajah User · Accepted Answer · 2012-09-18 07:34:50Z

0

SELECT        nvrEmpCode, nvrEmpName, @Businessunit AS varchar

FROM            tblHRIMS_EmployeeDetail
WHERE        (intCategoryID NOT IN (0, 1)) AND (nvrResigned = 'No') 
AND (nvrBusinessUnit = (CASE WHEN @Businessunit = '' THEN nvrBusinessunit ELSE @Businessunit END))

answered Sep 18, 2012 at 7:34

Kajah User

5895 gold badges17 silver badges57 bronze badges

1 Comment

user1512559 Over a year ago

But this query helps in DataSet while passing NULL. Thanks a lot.

Collectives™ on Stack Overflow

Static WHERE clause in SQLDatasource

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related