27 January 2006

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.

Posted on Friday, January 27, 2006 by Unknown

No comments

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.

Posted on Friday, January 27, 2006 by Unknown

No comments

23 January 2006

Tentatively the mind enters another expression space.

Posted on Monday, January 23, 2006 by Unknown

No comments