SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Wednesday, August 30, 2006

User defined functions and booleans?

SQL Apprentice Question
For some strange reason, I can't find any information on this... Can a UDF
return a boolean? I know there isn't a boolean type, but there sorta is...
The reason I ask is because it would make my UDFs more graceful, as in:

UDF GetSecurity:
Declare @SecID int
Set @SecID = Select SecId from Security where UserID = @user and blocked = 1
return @SecID

Select SecureStuff from SecuredTable where not exists (GetSecurity(@userId))

It would be much clearer to do:

UDF Blocked:
RETURN EXISTS(select SecId from Security where UserID= @user and blocked = 1)

Select SecureStuff from SecuredTable where not Blocked(@userId)

If this is possible, then what do I set the return type in the UDF as?
Boolean obviously isn't a valid choice, nor is bit...

Celko Answers
Now, we are getting to the real problem. You do not want to give up
programming in a procedural language style, with flags, subroutines,
loops, materialized local variables, temp tables to mimic scratch tapes
and all that jazz.

SELECT securestuff
FROM SecuredTable
(SELECT sec_id
FROM Security
WHERE user_id = @my_user
AND blocked <> 1);

SQL is declarative and has totally different style. Spreadsheets are
the only other declarative language most people use. You might was
well be speaking English with Japanese grammar.

Since you did not bother to post DDL, I am going to guess that
"blocked" is a bit flag with an obvious meaning. This is just like
we wrote in assembly language and very low level procedural code. You
will see a lot of newbies still using the "is_" naming
conventions from C.

In SQL the data would provide us with that information. For example,
we might record the date that a user_id was or will be retired and
compare it to the CURRENT_TIMESTAMP. When you use Boolean flags in a
schema, you must have CHECK() constraints to assure that the flag is
set properly - for example, that users have their blocked_flag = 1
when (expire_date < CURRENT_TIMESTAMP).

But wait a minute! The blocked_flag is redundant and the whole goal of
RDBMS is to get rid of data redundancies. This is one reason we do not
have Booleans; the other reason have to do with NULLs and 3VL.

I tell people it takes at least a year of full-time SQL coding to have
an epiphany and to throw out the old thought patterns.

No comments: