As a fairly well experienced database developer who's new to MS SQL Server, here are some links that have been useful to me so far:
SQLTeam.com: Introduction to Dynamic SQL Part 1
SQLTeam.com: Introduction to Dynamic SQL Part 2 - here sp_executesql is what I was really looking for, as it lets you execute dynamic SQL with bound parameters
Transact-SQL Data types reference (MS)
Translating from Oracle to SQL Server
SQL Server Stored Procedure Basics