Q:

SQL Use Declare to Omit Where Clause Items

Is there a way to exclude parts of a SQL Statement based on Declared Values?
For instance;
DECLARE @OnlyY as VARCHAR(1) = 'Y'

SELECT count(*) from main where IDATE > '2016-01-01' 

If @OnlyY = 'Y' THEN
    AND Qualify = 'Y'
END IF

In this case if @OnlyY isn't Y then the part in between the if/endif wouldn't happen at all.
The reason I need this is because I am porting an Access 97 application to .NET. In the Access 97 app there is a part that creates a temporary table and then generates a report from that table. The SQL involved is a huge set of if/then statements that remove data from the temporary table. I'm able to build the DataTable for viewing in a Datagridview. My issue is that I can't get a SSRS to have the same flexibility as .NET in the if/then statements.
So how should I go about doing this?

A:

One option you can look into is Dynamic SQL in which you can dynamically change anything you would need.
 DECLARE @SQL AS NVARCHAR(MAX)
 SET @SQL = N'SELECT COUNT(*) FROM main WHERE IDATE > ''2016-01-01'''

 IF @OnlyY = 'Y'
 BEGIN 
   SET @SQL += N' AND Qualify = ''Y'''
 END

 EXECUTE sp_executesql @SQL

