I’m sure one of us already Wiki’d this or put it in a blog but since I just had to weed through some code to figure out why something wasn’t working I figured I’d say it again.

PLEASE and I stress, PLEASE, make sure you initialize your variable in TSQL or at the least, use IsNull or coalesce to prevent this famous and all annoying issue from killing your hard work

Declare @AllImportantVarToRunThisScript varchar(30)
Declare @MyVarThatIKnowHasValue varchar(30)

Set @AllImportantVarToRunThisScript = (Select 'MyPretendCharColumn') + @MyVarThatIKnowHasValue

Select @AllImportantVarToRunThisScript

Oops! You just lost the game and crashed and burned. Take care of your NULL’s!!!!

Declare @AllImportantVarToRunThisScript varchar(30)
Declare @MyVarThatIKnowHasValue varchar(30)

Set @AllImportantVarToRunThisScript = (Select 'MyPretendCharColumn') + IsNull(@MyVarThatIKnowHasValue,'')
--Or
Set @AllImportantVarToRunThisScript = (Select 'MyPretendCharColumn') + coalesce(@MyVarThatIKnowHasValue,'')
--Or really hectic
Set @AllImportantVarToRunThisScript = (Select 'MyPretendCharColumn') 
				+ (Select Case When @MyVarThatIKnowHasValue Is Null Then '' End)

Select @AllImportantVarToRunThisScript

Ok, the last one is just silly and it can get even worse but it’s better than getting NULL 😉