Obscure problem

This has happened to me before, but naturally I forgot because it has been a while. The scenario is that you have dynamic SQL and you are trying to replace tags in your SQL string with actual values. This can lead to making the whole SQL string seemingly blank. Example code:

DECLARE @table VARCHAR(100) = NULL;
DECLARE @sql VARCHAR(MAX) = 'SELECT 1 FROM @table';

SET @sql = REPLACE(@sql, '@table', @table);

PRINT @sql;

This code is just for demonstration purposes where the @table tag is to be replaced with an @table value.

Gotcha

When you run the above SQL @sql is NULL, but when it prints out it appears to be blank because printing NULL gives you blank. Therefore it is important to note that if you are working with the REPLACE function that you cannot pass in NULL as a replacement value because it will make the whole string expression NULL (or what looks like blank during run time or debug).