SpurData Blog

/* borrowed 20150717 by wills - from pszanto at http://stackoverflow.com/a/13249209/377058 - made @Debug a parameter */ ALTER PROCEDURE [ dbo ].[ usp_Admin_TruncateNonEmptyTable ] @ TableToTruncate varchar ( 64 ), @ Debug bit = 1 AS BEGIN SET NOCOUNT ON -- GLOBAL VARIABLES DECLARE @ i int --DECLARE @Debug bit DECLARE @ Recycle bit DECLARE @ Verbose bit DECLARE @ TableName varchar ( 80 ) DECLARE @ ColumnName varchar ( 80 ) DECLARE @ ReferencedTableName varchar ( 80 ) DECLARE @ ReferencedColumnName varchar ( 80 ) DECLARE @ ConstraintName varchar ( 250 ) DECLARE @ CreateStatement varchar ( max ) DECLARE @ DropStatement varchar ( max ) DECLARE @ TruncateStatement varchar ( max ) DECLARE @ CreateStatementTemp varchar ( max ) DECLARE @ DropStatementTemp varchar ( max ) DECLARE @ TruncateStatementTemp varchar ( max ) DECLARE @ Statement varchar ( max ) -- 1 = Will not execute statement

I'll try to expand on this, but anyway, here's some examples about how you have to be careful with IN and NOT IN in T-SQL in terms of when results will be returned. If the list you're comparing to has nulls, watch out. If the item you're comparing has nulls, watch out. If the list could be empty, check for that. IN and NOT IN may behave differently than you expect in all these situations. Below are most of the iterations. --in select 'row' where 1 in ( 1 , 2 , 3 ) --returns select 'row' where 1 in ( select 1 union select 2 ) --returns select 'row' where 1 in ( select null union select 2 ) --does NOT return - cannot determine if 1 "in" null, maybe it is, who knows? select 'row' where 1 in ( select top 0 f1 from ( select 1 f1 union select 2 ) qry ) --NO return b/c the top 0 qry returns "nothing", and 1 is NOT "in" nothing ("in nothing" = false) --so