SQL - Adding/Disabling a Where clause based on a boolean condition in Select Statement (ASP.net SQLDataSource)

Question

I have a SQL select statement that is basically just

SELECT * FROM myTable 
WHERE status != 5

It is a lot more complex than in reality, but its mostly convoluted column names and joins.

what i want to be able to do is on a parameter being a value, I disable the where clause, i.e.

SELECT * FROM myTable 
where CASE WHEN @parameter = 'true'
THEN --...add the where clause ('status != 5')

It doesn't work like that but does anyone have a different method of approach? i'm using ASP.net with the select in an SQLDatasource if that opens up any options. It's to do with hiding particular records from non-logged in users but showing others.

TL;DR: Can I enable/disable a where clause based on a parameter in SQL or ASP.net

Answer 1

You could do literally what you're asking (i.e. disable the where clause) using dynamic sql.

declare @ssql varchar(max)
set @ssql = 'SELECT * FROM myTable' 
+ CASE WHEN @parameter = 'true' THEN ' WHERE status != 5' ELSE '' END
EXEC (@ssql)

However, it will be easier to read and modify in future if you can avoid dynamic sql.

Since you need to filter status where param = true, but when param = false you don't want to filter on status, your where clause should look like this:

WHERE (@parameter=True AND status!=5) OR (@parameter=false)