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
