Kayot Kayot - 5 months ago 9
Vb.net Question

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?

Answer

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'
   SET @SQL += N' AND Qualify = ''Y'''
 END IF

 EXECUTE sp_executesql @SQL
Comments