NULLIF()

NULLIF() returns NULL if the two parameters provided are equal; otherwise, the value of the first parameter is returned.  Seems a little odd and not very useful, but it is a great way of ensuring that empty strings are always returned as NULLS.  

For example, the expression:

nullif(@variable1,'')

will never return an empty string, it will return either a NULL value or a string with at least one character present.  Also,  SQL ignores trailing spaces when comparing strings, so even if the string isn't empty but it contains all spaces, it will still return NULL.

select nullif('     ','') 
 
----
NULL

NULLIF() can be a very useful function to employ.  Consider it when you need to replace default values other than just NULL when using ISNULL() or COALESCE() expressions.

http://msdn.microsoft.com/en-us/library/aa276840(v=sql.80).aspx

Avoid empty reports in a reporting services data driven subscription

        Reporting Services provides data-driven subscriptions so that you can customize the distribution of a report based on dynamic subscriber data.  Data-driven subscriptions are intended for the following kinds of scenarios:

 Distributing reports to a large recipient pool whose membership may change from one distribution to the next. For example, distribute a monthly report to all employees.

Distributing reports to a specific group of recipients based on predefined criteria. For example, send a sales performance report to the top ten sales managers in an organization.

This is an excellent feature except that there is no clean way to stop empty emails from being sent, as in the case when your query returns an empty dataset. 

There are many hacks that you can try

First, When configuring a data driven subscription, you must provide a query which returns subscriber data. Most of the time this query simply returns rows from a table which lists your data driven subscription users and their preferences around delivery and parameter values for the report in question. Each row of data returned equals one report we'll deliver as part of the subscription. Just modify this query so that it also filters the result based on whether or not the report itself will return rows. For example:

SELECT * from SubscriptionTable

WHERE EXISTS(SELECT Field1 FROM DataSourceTable WHERE Field2 Between DateAdd(dd,-2,GetDate()) and GetDate())

If you provide this query to the wizard, it will only return subscribers for whom when there is data you wish to report on. (records in the DataSourceTable table that have a date within the last 2 days)

        In Server Management Studio under the list of jobs, you will find the subscription you created with its GUID.  The first step in the job is an EXEC command that will run the SSRS subscription. Edit this job and add a step ahead of the SSRS step.  This step does a SELECT 1/(SELECT COUNT(*) FROM MyDataSet).  If this step fails (because there is no data in the dataset the job exits reporting success, and if it succeeds, the SSRS subscription is run.

Use a RAISEERROR statement in your SQL script or procedure. In the case of an error the report will not be rendered.

IF NOT EXISTS ( SELECT * FROM MyDataTable)

RAISEERROR('no records found....)

ELSE

SELECT * FROM MyDataTable