CODEWITHSUNDEEP
sql-server-2008select
Dotnet ReSource
Dotnet ReSource

Reputation: 201

How to write the select statement in this condition?

I have a query which i am using to filter the grid 

     SELECT * FROM Resources
              WHERE ResourceName  ='" + ResourceName + "' 
              AND  Status = '" + status + "' "

and my grid looks like this

  ResourceID|ResourceName|Status

I had added the ResorceName and Status in a dropdown for filtering the grid now my problem is that in this select statement if any of the paramaters is null the data is not Binded to the grid but if I pass both the parameters it filters the grid and gives the required row or filtered row from the grid... Can anyone tell me how do I write select statement if any of the parameter is null.

Upvotes: 0

Views: 415

Answers (3)

indiPy
indiPy

Reputation: 8072

SELECT * FROM Resources
WHERE (ResourceName  = CASE WHEN '" + ResourceName + "' IS NULL THEN ResourceName  ELSE '" + ResourceName + "' END) //do same for other parameter

Upvotes: 0

Martin Milan
Martin Milan

Reputation: 6390

In terms of fixing your problem quickly, something like this would work...

Select * From Resources Where (ResourceName = '"+ ResourceName + "' OR ResourceName IS NULL) AND (Status = '" + Status +"' OR Status IS NULL)

That however is NOT an acceptable piece of code, as it is vulnerable to SQL injection. In essence, suppose the ResourceName that is passed in is 

'; Drop Table Resources; --

You probably don't need me to tell you what that does.

My advice is to ALWAYS make use of SQLCommand objects in .Net - also known as "Prepared Statements" in other languages. It prevents these kind of tricks...

Upvotes: 1

Phil Murray
Phil Murray

Reputation: 6554

Have a look it the below post on catch all queries

Catch All Examples

Upvotes: 1

Related Questions