Trump card

Eugene's response to this post is one of the most amusing refutations I have ever seen on the net. I won't steal its thunder by quoting, just give it a read if you have 30 seconds to spare.

Read More

MS SQL: Functions and Procedures

Functions and procedures in MS SQL 2000 don't play particularly well together. That's not really a problem, seeing as they both solve the same problem: running a sequence of operations. But (and this is a particularly large but) functions offer only a subset of the range of operations available — most notably, they can't alter data in tables or create temporary tables. So why would anyone use a function? They're particularly useful as they can return values. Unlike stored procedures which return an error-code and can pack output variables with data; functions return a value in much the same way as similar constructs in procedural languages. The 'value' returned by a function can be a scalar value (int, bit, char, &c.) or.. a table. Scalar-valued functions are useful in a whole bunch of places, but the table-valued function is an interesting beastie. Table-valued functions behave like views with arguments, so for example:

a) select question from questions_view where answer=42
b) select question from questions_by_planet('earth') where answer=42

In a) above, all planets would be interrogated for possible questions, those matched to possible answers, then filtered by the where clause. In b) interrogation could be performed only on earth (saving a few planets along the way). In such scenarios, using a function over a view is far more efficient. Right about now Mr S. Procedure's tappping me on the shoulder to say: "Hey buddy, I do that too.". Yes, okay, you can achieve the same thing with stored procedures, but they don't provide as elegant a solution. Functions can literally be dropped in the place of views as if they were merely a view with a more complex name; to return a table from a procedure requires (to the best of my knowledge) a pretty dance involving creating cursors into temporary tables. Why did I feel the need to write all this down? Well, apart from the eminent utility of another description of the differences between functions and stored procedures in MS SQL, I intend to use it as a base from which to describe my recent problems and their resolution.

Read More

Tentatively the mind enters another expression space.

Read More