SpurData Blog

Here's a script to generate alter column statements for all the audit fields in your database. In this case we are wanting to convert all the integer columns to varchar. select 'alter table ' + TABLE_NAME + ' alter column ' + COLUMN_NAME + ' varchar(50) null;' from INFORMATION_SCHEMA . COLUMNS where COLUMN_NAME in ( 'InsertedBy' , 'UpdatedBy' ) and DATA_TYPE = 'int' Quick review of Alter Table Alter Column available at SQL Server Planet: http://sqlserverplanet.com/ddl/alter-table-alter-column

I learned something about T-SQL variable scoping that I did not previously recognize. Within a loop, you can declare a variable. When you run the loop, things function fine - you don't get any "hey, you already declared that variable" errors. In my mind that means the variable must be locally scoped to the loop, and perhaps would get reset on each pass through the loop then.  But that's not quite the case. Try this and look at the output. It's only instantiating the variable on the first pass. Works the same on table variables, which is what I was actually curious about...

/* 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...

Little disciplines to remember when programming T-SQL. Structured programming practices: Find what varies and encapsulate it Keep your procedures small; let each do one thing and do it well Favor Clarity over Cleverness Clever solutions are being scrapped in IT shops all over the world every day and replaced with less "clever" solutions that are understandable and maintainable. Usually Consistent is better than Better. (Sad but true.) Simply formatted header comments are maintained; complex formats are not. Example of a simple header: /* Note on what the proc does. usage: exec usp_MyProc created YYYYMMDD by username updated YYYYMMDD by username - chg# xxxx: description */ In Reporting queries, prefer #Temp Tables over @Table Variables. Table variables are easier to pass around, but the optimizer always assumes there is only one row. For larger temp storage (>100 rows), use #temp tables which produce real statistics. Set vs. Select - prefer Set Assume @x = ...