Transact-SQL statements can be grouped together in batches, they can persist in the database, and they can repeatedly execute as stored procedures or return values within a statement as functions. It is essential that you understand the differences between these programming constructs and that you understand that their actions are not mutually exclusive.
Transact-SQL routines can be quite complex, and they can become a significant portion of your application's source code. However, the hardest part of writing routines is writing the SQL code that they are composed of.