.NET Stored Procs

One of the bigger features of SQL Server 2005 is it's integration with the .NET platform. Now there is the ability to write your stored procs with a language like C#.

Now I'm just wondering how much of a good idea this is. Every scenario I try to go through still leads me to the same conclusion. This is, it may be handy for those really complex procedures that require some kind of funky processing or business logic or need to access the internet or a COM object or something. But for the most part, a loop is a loop, and an insert statement is still done in SQL, so I'm guessing that 90% of the stored procs required for a program are going to be in straight SQL.

When I originally heard about it, I imagined there was some 'magical' object relation with the data, but it turns out its just a bit of .NET code running on the SQL Server. Sounds much like Oracle's ability to run stored procs written in Java. Maybe the real scare factor here is it promotes the idea of generating sql from within C# code. To me, unless it's done right, there's no better way to clutter the source code and make the application hard to debug and maintain then having it "dynamically" generate its own sql code.

I can see the positives, but the potential negatives are riding right up there as well.

General .NET
Posted by: Brendan Kowitz
Last revised: 21 Sep 2013 12:15PM


5/1/2006 11:13:10 PM
On your point about dynamically generating the SQL code, whats the difference doing it in .net or doing in it in the stored procedure? I'd personally prefer to see it in the stored procedure simply to have that separation; however if you're going to be embedding .net into your SQL Server, then it 'is' the stored procedure - at which point, what is the difference?

Until someone can show me a common, really good reason to be writing a stored procedure in .net, I just see it as overhead that isn't required. As you point out, in the scenarios where you are accessing external components/objects/data - that might be a perfect scenario.

The other place I can see it being quite groovy is if you could accessed cached data (which was cached via your application) through the stored procedure. If you could do that, then when applied correctly you could be significantly reducing the workload on your database.

The issue I see with it in general is that people might start writing .net stored procedures and not really know what they are doing and the impact that it might be having. That said, you could just as easily give them T-SQL and have them destroy your database just the same.
8/23/2006 6:54:40 PM
I think that only 5% out of the projects that worked on could be simplified by this.

No new comments are allowed on this post.