CLR Stored Procedures is a relatively new way to build extensions for your database. Traditionally stored procedure logic has been written in SQL, but SQL Server 2005 introduced the ability to use CLR code for procedures.
Need special math functions not supported by SQL? Want to handle a storage type not handled by SQL? Want to write a trigger to do things beyond what a traditional SQL Trigger can accomplish? Want to lookup a domain name in DNS?
Use a CLR Proc to handle things like specialized encryption, archive, remote connections, specialized business logic, pull an RSS feed, request data from another database, authenticate a user against a domain, I think you get the idea. Anything that you can do in managed code can be done in a CLR Proc.
CLR procs are still viewed as somewhat suspicious among a lot of DBAs. I think this is mostly because they can't see what the function is going to do, so there is some fear of losing control. CLR integration has to be enabled by the admin on SQL Server for it to work. VistaDB always supports CLR Procs because we live within your application, there is no security concerns for us.
T-SQL Procs are collections of SQL statements that are executed in a batch as a function. They are best when you have little logic involved and are only wanting to somehow project the data into a new row structure. They are not very efficient at operations involving lots of logic operations.
CLR Procs are written in .Net and are much faster at executing complex logic chains, or when you need to take advantage of abilities not found in SQL. As an example if you want to take a string and convert it to another language through a web service you could not do this in SQL.
The NUnit samples included in the setup include a single assembly with multiple types of CLR Procs. There is a sample CLR Proc project that is built and inserted into a database for testing through the NUnit application. These are more advanced than the basic samples included here in the tutorials area of the site.