Thursday, December 4, 2008

Scary – SyBase

In my opinion, SQL Server is the most complete DBMS. For the past couple of years, I've worked with it from the basic T-SQL to the most administrative clustering, security and so on.
But despite my preferences (Microsoft), it's always good to keep your mind open to other solutions around you. For instance, I also appreciate MySQL, which is a great solution for small business (free).

The last month, however, I've worked with SyBase. SyBase is known by many people as being the "cousin" of SQL Server for historical reasons. Of course, the syntax isn't much different from SQL Server, but the tiniest difference can have a big impact. Take the following example of a stored procedure in SQL Server:

Stored Procedure

You can call this procedure passing no parameters or one:

And that's it. If you pass more than one parameter, the SQL Server Management Studio will yield an error message:

Msg 8144, Level 16, State 2, Procedure DefaultParameter, Line 0
Procedure or function DefaultParameter has too many arguments specified.

Now, the same procedure can be written for SyBase... exactly the same! However, in SyBase, you can call the procedure with "n" arguments:

The DBMS will take just the number of arguments that the procedure wants, ignoring the rest of them. This is quite odd!

The problem I had at work was that someone created a procedure with 2 parameters, both varchar. Then, all the other stored procedures were written to call this procedure with two varchar parameters. Somewhere in time, the second parameter was no longer required and was removed from the procedure definition. Of course, the code that called this procedure kept running because SyBase allows the procedure to request just one parameter and the caller to pass two parameters.

My task was to add a new output parameter to the procedure. It now had 2 parameters again, but the one I added was of type integer. Can you imagine what happened to all the calls to the procedure? Bum! They where passing two varchar variables and now the procedure is expecting one varchar and one integer. Scary...

I haven't searched for an explanation for this behaviour (nor I'm going to) because I'm sure it won’t convince me. So, that's one point less to the SQL Server cousin.