About a year or so ago, SQL Server Magazine (SQLMag.com) came out with a list of free downloads for SQL Server [1]; it included both totally free as well as trial downloads, all of which were lots of neat third party add-ins for SQL Server. I thought it would be cool to review a bunch of them to see which ones might be really great to have in my toolbox, so I'm going to start a little blog series on them. SQL Mag listed them, but really didn't talk much about them, other than a brief comment. I'll go further than SQLMag did by looking into them a little more in depth, and actually investigate some of them. I hope that you find this an interesting series.
This time I was interested in checking out the Stored Procedure Generator by DelphiAssistant; when you’ve worked on some large teams as I have doing some heavy-duty coding, you need a tool or a set of templates to quickly gen up stored procedures. One time I wrote over 70 of them for a process in no-time flat because I had a good template to build them from. Also, because the .Net guys are writing some really great stuff these days, you can really assist them out as well for their “business logic layers” or “data access layers” by creating the procedures that they need to go along with their middle-tier objects. Oftentimes these are very simple to write, and because of this, you need to create many of them.
Go to codeplex, download the product, and unzip the SPGen_ReleaseCandidate1_Binaries.zip file. Click on the exee and a nice looking wizard pops up. You’re given a choice to connect to a database server using Windows or SQL authentication, so because of my setup with my domain and my company laptop I decide to go with SQL auth for this. I type in my user, and then type in my password…which stares me in the face in clear text. Oops. Sort of surprised of this, it’s not really a biggie, but just noticed it right off the bat for sure.
Pointing to a few different databases, I have to be honest…I first looked at one of my VMs (SQL 2008 Dev Edition running on Windows Server 2008 R2 virtual machines) and I kept getting errors. I did not investigate them closely, but also did not resolve, so I ended up moving to another server which wasn’t a VM by the way. I’ll have to look into the error and see what it’s complaining about later and let you know, but I had to move to another server for my test.
So when you click “Generate Stored Procedures” you have two output windows, one for SQL, and the other a C# Wrapper Class. While the classes gen’d are ok, I probably won’t be using them; you can check them out and see if they will help you with your project.
As far as the SQL Script goes, he does a decent job with the code – when you fire the generator up you get a SELECT, INSERT, UPDATE, and DELETE procedure, and a couple of others to affect multiple rows. Very nice. One thing that I found interesting was that the author doesn’t give you the chance to enter your own name and description, but rather gives you this:
-- ===================================================================
-- Entity Name: sp_Customer_SelectAll
-- Author: Mehdi Keramati
-- Create date: 4/25/2010 10:13:03 PM
-- Description: This stored procedure is intended for selecting all rows from Customer table
-- ===================================================================
I would have rather seen an input box for this, along with your own procedure name. sp_ is probably not the best prefix for SQL Server stored procedures either, but the tool defaulted this. This is fine if you “copy to clipboard”, but if you “run SQL Script Against Server” you’ll get some names and descriptions that you probably don’t want loaded onto your database server. Another obstacle, but no biggie
One thing that I did not like was the BEGIN – END blocks for stored procedures, because if you are debugging, you may have to remove the BEGIN if you don’t highlight the entire stored procedure code and include the END. It’s a nit-pick, sure, but I don’t do it. If the code is long you have to remember that the END is stuck down at the bottom. Here’s one of the procedures:
Create Procedure sp_Customer_Insert
@CustId int,
@CustFirst varchar(50),
@CustLast varchar(50),
@Randomizer float,
@LastLogin datetime
As
Begin
Insert Into Customer
([CustId],[CustFirst],[CustLast],[Randomizer],[LastLogin])
Values
(@CustId,@CustFirst,@CustLast,@Randomizer,@LastLogin)
End
GO
Decent? Yes, workable for sure. One more thing that I would like to have seen, though, is all of the reserved words capitalized; you can see that they’re just mixed case with all of the other code. Once again, no biggie but a nice to have for sure. (Unless it's a case-sensitive database. I'm actually using one at work these days).
Here’s one of his SELECT ALL scripts:
-- ==========================================================================================
-- Entity Name: sp_Customer_SelectAll
-- Author: Mehdi Keramati
-- Create date: 4/25/2010 10:13:03 PM
-- Description: This stored procedure is intended for selecting all rows from Customer
-- ==========================================================================================
Create Procedure sp_Customer_SelectAll
As
Begin
Select
[CustId],
[CustFirst],
[CustLast],
[Randomizer],
[LastLogin]
From Customer
End
GO
In conclusion, the tool is a fairly simple one to download and use. The scripts are not overly-complicated, and the formatting is decent. Some of the little things that I would have liked to have seen are definitely not deal breakers here. While some of the output is fairly simple, it’s effective and the tool could be used to assist in quick development of SQL Server DML. I suggest that you give it a go the next time you need to write a bunch of stored procedures.
Thanks for reading,
Lee
-----------------------
Dean Vernon Wormer: The time has come for someone to put his foot down. And that foot is me.
spgen: http://spgen.codeplex.com/
[1] Keller, Megan (2009). Mega Guide to FREE SQL Server Tools. SQL Server Magazine. Penton:August, 2009
