Traditionally from many year we have build sql server stored procedure with the transact sql. Microsoft framework 2.0 and sql server 2005 allows us to write stored procedure with other languages like C#.NET,VB.NET etc.
Microsoft sql server 2005 provide integrated support with .net common language run time so we can create stored procedure in C#.NET,VB.NET also. It has several advantages over using transact sql like compiled execution,security and type safety.
I have found a great articles that describes all the way to develop stored procedure with c#.net.
Mr. Bipin joshi describes all the tips and tracks of how to develop stored procedure with c#.net
here is the link for that article.