Sometimes before I actually run dynamic SQL statement I want to know if there is any data, making it worthwhile do run it.
Here's some code that will test the T-SQL for data.
DECLARE @vcSQL VARCHAR(8000)
DECLARE @nvcSQL NVARCHAR(4000),
@nvcParamDef NVARCHAR(128),
@bEXISTS bit
-- Change the IF statement to test having data vs. not having data.
IF 'EMPTY' = 'EMPTYx'
SELECT @vcSQL = 'SELECT * FROM pubs.dbo.dtproperties'
ELSE
SELECT @vcSQL = 'SELECT * FROM pubs.dbo.authors'
SELECT @nvcSQL = 'IF EXISTS (' + @vcSQL +
') SELECT @bEXISTS = 1 ELSE SELECT @bEXISTS = 0',
@nvcParamDef = N'@bEXISTS int OUTPUT'
EXECUTE sp_executesql
@nvcSQL,
@nvcParamDef,
@bEXISTS=@bEXISTS OUTPUT
IF @bEXISTS = 1
PRINT 'Dynamic SQL [' + @vcSQL + '] data exists'
ELSE
PRINT 'Dynamic SQL [' + @vcSQL + '] has no data'