An ADO and VBScript Adventure
Saturday, October 28, 2006

In a recent work-related project I was asked to provide a simple VBScript code snippet that called a SQL stored procedure. No one knew what the stored proc looked like or even how it worked. To make matters worse, I am neither a T-SQL nor VBScript guru by any degree. The task of writing that small bit of code is the topic of this article. If you are a VBScript or ADO guru, you can stop here as you'll probably not learn anything. You might get a laugh out of it though. ;-) If you are new to writing ADO code in VBScript (as I was), read on as I'll show you some pitfalls and things I didn't know (but unfortunately hit).

The stored procedure in question lived on a SQL Server 2000 machine, and to my surprise, contained over 100 parameters (all of which contained "default" values but 1)! The only required parameter was a GUID and was easily found in another table. So I figured I would test the stored proc out first in Microsoft's SQL Query Analyzer and make sure it worked as described. With my very little SQL experience, I just created the syntax that would call the stored proc and pass in 1 GUID and 99 NULL values. I thought that a NULL value parameter would cause the stored proc to use its default value. Oh how I was wrong! Instead of using default values, NULL was treated as an acceptable value and therefore passed in to the remaining 99 parameters. You can imagine the damage that was done. I basically had used this stored proc to set every one of this table's foreign keys to NULL. So I pretty much destroyed the database. Nice... After many database restores, a coworker explained to me the problem with using NULL. The correct thing to do was to pass the T-SQL keyword DEFAULT as the parameter value and the stored proc will then use its default values (if any). Man, I felt like an idiot. What programmer thinks that NULL wouldn't be an acceptable value? ;-) Even though I was thoroughly embarrassed, I figured the frustration was over and that I was golden. So I proceeded to write the sample VBScript which would do the same thing as the code in Query Analyzer. Here's a subset of the code I wrote (error checking removed for simplicity):

Const adCmdStoredProc = 4
Const adGUID = 72
Const adParamInput = 1
Const adVarChar = 200
Const MyGuid = "1A8A3DA2-1564-40FA-8F4B-4B27B5B2BAD5"

'# Create your connection object to the SQL database
Dim objConnect
Set objConnect = CreateObject("ADODB.Connection")
objConnect.ConnectionString = "Provider=SQLOLEDB; " &_
                              "Data Source=MyServer; " &_
                              "Initial Catalog=MyDb; " &_
                              "Trusted_Connection=yes"

'# Setup your command object that will call the stored proc
Dim objCommand
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnect
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "MyStoredProcName"

'# Supply the required parameters to the stored proc
Dim objParam1
Set objParam1 = objCommand.CreateParameter("@Param1", _
                                           adGUID, _
                                           adParamInput, _
                                           , _
                                           MyGuid)
objCommand.Parameters.Append objParam1

Dim objParam2
Set objParam2 = objCommand.CreateParameter("@Param2", _
                                           adVarChar, _
                                           adParamInput, _
                                           32, _
                                           "BlahBlah")
objCommand.Parameters.Append objParam2

objCommand.Execute

'# Tada!  It should work right?

As you can see, I created ADODB.Connection and ADODB.Command objects. I then passed in only those values that I needed (just 2 in this case) for the stored proc to execute. The first of which was the only parameter that was required by the stored procedure. When I ran the code, all I did was foul up the database again. The parameters I supplied were passed in as the first and second parameters to the stored proc. It didn't even matter that I supplied the name of parameter as well. Wait, how can that be?!! Well of course, I was fuming after this. The deadline had passed and people were beginning to wonder why this little snippet of code wasn't complete yet. Drama sucks! After a few cigarettes and some time to calm down, I sat down with my favorite search tool. Google. So, as usual, I started googling, googling and more googling. On about the 5th try, I found a hit about 20 links down (who looks down 20 hits on a Google search?) that showed how to specify your Command.Parameters collection when you want to use the default value for some of the parameters. Here's the fix:

...
Dim objCommand
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnect
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "MyStoredProcName"
objCommand.Refresh
...
objCommand.Parameters("@Param1") = MyGuid
objCommand.Parameters("@Param2") = "BlahBlah"
objCommand.Execute

The Refresh routine queries the database for the parameter list before you make your query. This means that you don't have to "create" any parameter objects for the Parameter collection that is in the Command object. All you have to do is assign a value to the parameters that don't define a default value. Everything else not assigned a value to will not be set and therefore the stored proc will use the default values it defined. So as usual, I learned something new. Oh well, I'm not a big fan of VBScript or stored procedures anyway. In fact, I think languages that aren't compiled were created by the Devil. ;-) Just kidding, they have their place and uses I guess. Anyways, here's what I can pass on to you (unless you just read this for a good laugh):

NULL is a valid value type when passed into a stored procedure parameter and therefore does override the default value (if one is defined). In a tool like Query Analyzer, you need to specify either the DEFAULT keyword, or the @ParamName=DEFAULT alternative syntax. I'm sure there might be other ways to do this, but those are the ones I found.
In VBScript, make sure you query the database for a list of parameters before you try and pass them to a stored procedure (unless there are no parameters of course). You do this with the ADODB.Command.Parameters.Refresh routine, and then you will be able to supply only those parameters you need and the rest will be treated as having no value at all. A side benefit is not having to write the code that creates and defines those parameter objects.

This might have been a silly article, but maybe by some freak chance I'm not the only person in the world who didn't know that stuff. Hopefully somebody else will find it useful. Many more articles like this and people won't read this blog anymore. ;-) Until next time...

- Gilemonster

Labels: General