Freigeben über

Visual Basic Stored Procedures in the Real World

  • Artikel

During my consulting days I spent about a year and half as a database programmer writing T-SQL stored procedures.  Often times the application developers would dump requirements down to the database developer that should have been done in the front end C++ code (Middleware was just coming into fashion at this time) but since they didn't want to do this work they would toss it over the wall to the database.  I got very good at writing stored procedures that were over a thousand lines.

Had the project had a good architect that understood the big picture this might never have happened but as it was I learned to do just about anything with T-SQL that you could think of.  There was a lot more then just select, update, and delete in those procedures, they held a huge bulk of procedural business logic code.

What I would have given for a copy of SQL Server 2005 during that project.  This would have been tough since SQL 6.5 was the latest and greatest but would I would have given anything to be able to just slap some VB code in those procedures.  Being a VB developer I would get very frustrated with the limits of T-SQL.

If you are not aware SQL Server 2005 has an embedded CLR that allows you to write stored procedures in T-SQL or any .NET language like VB or C#.

This simple fact of database stored procedures based on something other than T-SQL seems to have caused a bit of confusion in the developer world.  People that I talk to about this don't seem to know what to make of this or what it really means to them.

Just prior to joining Microsoft a month ago I did very large "prototype" project for a company that was based on SQL Server 2005.  Despite playing with the BETA bits as a curiosity I didn't really know what to make of these VB based procedure either, building a full applications cleared things up greatly.

The first thing I can tell you, if you don't already know, is that most of your stored procedures will still be written in T-SQL in the future.  The reason is that it is still much easier and productive to write a simple select, insert, update, or delete statement in T-SQL then in any other language.

To write this in T-SQL you are doing 1 line.  To do this in a VB procedure you need code to open the connection, generate the same T-SQL statement to do the command, execute it, and code to return the results.  Too much code for a simple step and no advantage other then impressing your friends with your great knowledge.  For me I always preferred the solution that solves the business problem with the least amount of effort and has some level of maintainability.

I found that over 85% of my procedures where just like they where before.  The other 15% we greatly enhanced.  These were reporting, search, and procedural business logic type procedures.  Depending on the complexity your application this percentage may be more.  This application did lots of raw data storage and limited reporting and work flow.

Some examples of VB based procedures that were much easier to write then doing the same thing in T-SQL included things like job routing.  The application required jobs to be routed around based on multiple criteria and states.  Doing the code in VB allowed me a higher level language to control the very complex flow inside of this procedure.

Another example as working with time spans.  One of the important items the application tracked was time of a job.  This was easily done with a TimeSpan object in .NET.  Since SQL Server doesn't have a timespan data type I had to convert everything to strings to store in the database.  For various reports I needed to sum these TimeSpans.  Using VB this was simple.  I read the rows in off the table, created a TimeSpans and loaded it with the string, and used the TimeSpan.Add method to keep a running total.  At the end of the procedure I was easily able to do a TimeSpan.toString to return the results.

I could have done this in today by returning the data to the middle tier and working with it there but by embedding my code directly in the database I was able to save all of this network traffic and unload some of this processing to the database where it belonged.

Understand that writting your stored procedure in VB is not going to be event that changes everything you do on the database.  Rather it is another tool in your toolbox that you can pull out at the right time.  When your procedures call for branching or logic type code it is a good time to think about using VB instead of T-SQL.

Some good articles on this topic can be found here.

Comments