Selecting from a column that can equal anything

Question

this is my current code

SELECT * FROM [Products] WHERE CategoryID=@category

I'm using ASP.net and c#, where I set a session from page1 then redirect to page2 then have this session used as category. The problem is I want when the sessions null (ie if you go directly to the page, not from a redirect on page1) for the code to work like below

SELECT * FROM [Products] WHERE CategoryID= anything

So all of my products show up, not just the ones of a specific category. Is there anyway I can do this?

Thanks.

Is this MySQL? Probably not; table names in square brackets don't work in MySQL. Could you edit your tags appropriately? — O. Jones, Apr 28 '14 at 00:41
refer to this [answer](http://stackoverflow.com/a/13474228/3286163). — singularhum, Apr 28 '14 at 01:21

score 1 · Answer 1 · answered Apr 28 '14 at 00:48

1

If @category is set, run your query. If not, run this one:

SELECT * FROM Products

answered Apr 28 '14 at 00:48

JustinParker

628
6
13

score 0 · Answer 2 · answered Apr 28 '14 at 00:42

0

You will need a different query, one that omits CategoryID= whatever entirely, to do what you're trying to do. That's easy enough to program.

answered Apr 28 '14 at 00:42

O. Jones

103,626
17
118
172

Corey · Answer 3 · 2014-04-28T02:17:22.483

No, there is no value you can pass that will turn that = into <>. You have to use a different query for this, either a distinct piece of code or a query built in pieces dependent on the requirements.

Personally I'd use LINQ for simplicity:

var qry = context.products.AsQueryable();
if (!string.IsNullOrEmpty(session))
    qry = qry.Where(p => p.CategoryID == session);

You can go on adding terms like this, then execute the query at the end to get the results.

You can get a similar flexibility by creating the query as a string and parameter collection:

var qstr = "SELECT * FROM Products";
var parms = new Dictionary<string, object>();

if (!string.IsNullOrEmpty(session))
{
    qstr += " WHERE CategoryID = @category";
    parms["category"] = session;
}

using (var cmd = connection.CreateCommand())
{
    cmd.CommandType  = CommandType.Text;
    cmd.CommandText = qstr;

    foreach (var parm in parms)
        cmd.Parameters.AddWithValue("@" + parm.Key, parm.Value);

    using (var reader = cmd.ExecuteReader())
    {
        //....
    }   
}

If you need more than the one form then you can add some logic to build a list of conditions. Just be careful how you stitch them together.

Still prefer the shorter LINQ solution :P

Selecting from a column that can equal anything

3 Answers3