|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
User defined functions and booleans?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 Use: 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) Use: 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... William Sullivan (WilliamSulli***@discussions.microsoft.com) writes:
Show quote > For some strange reason, I can't find any information on this... Can a No, this is not possible. > 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 > > Use: > Select SecureStuff from SecuredTable where not exists > (GetSecurity(@userId)) You have however write a table function, and then say: IF EXISTS (SELECT * FROM GetSecurity(@userID)) ... But this is less useful in a WHERE condition, particular on SQL 2000. Better is to simply include the expression in the WHERE clause. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 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 WHERE EXISTS (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_<status>" 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. "--CELKO--" <jcelko***@earthlink.net> wrote in message Doesn't ANSI SQL:1999 explicitly define a Boolean Datatype?news:1155916244.566180.253180@p79g2000cwp.googlegroups.com... > 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. >> Doesn't ANSI SQL:1999 explicitly define a Boolean Datatype? << Yep, and it is considered "outside the core" in SQL-99 and the SQL-200xfamily. That is where you put a feature that you want to kill. One problem is that it has to be NOT NULL, which violates the principle that all data types in SQL are NULL-able, that NULLs propagate, etc. I have no idea how it got into the specs in the first place. What you can use is the IS [NOT] [TRUE | FALSE | UNKNOWN] predicate in Standard SQL that almost nobody has. "--CELKO--" <jcelko***@earthlink.net> wrote in message Yes, they seemed to have kludged a workaround for NULL by specifying that news:1156005970.961237.117130@m79g2000cwm.googlegroups.com... >>> Doesn't ANSI SQL:1999 explicitly define a Boolean Datatype? << > > Yep, and it is considered "outside the core" in SQL-99 and the SQL-200x > family. That is where you put a feature that you want to kill. One > problem is that it has to be NOT NULL, which violates the principle > that all data types in SQL are NULL-able, that NULLs propagate, etc. I > have no idea how it got into the specs in the first place. > > What you can use is the IS [NOT] [TRUE | FALSE | UNKNOWN] predicate in > Standard SQL that almost nobody has. "UNKNOWN" is equivalent to "NULL" in the spec. I'd like to see how they end up resolving that myself. On 19 Aug 2006 09:46:11 -0700, --CELKO-- wrote:
>>> Doesn't ANSI SQL:1999 explicitly define a Boolean Datatype? << Hi Joe,> >Yep, and it is considered "outside the core" in SQL-99 and the SQL-200x >family. That is where you put a feature that you want to kill. I don't understand this at all. There was no boolean datatype in SQL-92 and there is one in SQL-99. So it has be added to the latter standard. Whhy would anyone _ADD_ a feature too a standard, but _IMMEDIATELY_ put it on the list of features they want to kill? Why bother adding it at all, then? -- Hugo Kornelis, SQL Server MVP >> Why would anyone _ADD_ a feature too a standard, but _IMMEDIATELY_ put it on the list of features they want to kill? Why bother adding it at all, then? << Ever worked on a committee? :)We changed the definition of BETWEEN once. The SQL3 document had about 100 known flaws when we tried to add OO stuff that had to be removed. SQL-86 had a GRANT statement, but no REVOKE. Etc. I do not know for sure, but I would guess that some new guy put BOOLEANs into the draft document. In the old days Bruce Horowitz, the logician from Bell Labs would have written a paper the next meeting and demonstrated the problems in about one page with a simple example. "--CELKO--" <jcelko***@earthlink.net> wrote in message "A camel is a horse designed by committee"news:1156025912.991936.157010@74g2000cwt.googlegroups.com... > Ever worked on a committee? :) Is a bad act good because the audience can't tell the difference?:)
You continue to play the part of Pat Roberston vs. the theory of evolution. Shouldn't it be other way around?:) Now see inline:) "--CELKO--" <jcelko***@earthlink.net> wrote in message Really? What your suggesting to people is if they could write:news:1155916244.566180.253180@p79g2000cwp.googlegroups.com... > > 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. If x=y then write 'HELLO!' where x and y are TABLES, this is to discouraged. Church of Sql speaks:) > SQL is declarative and has totally different style. Spreadsheets are Impedence MisMatch. This is your classic client/server. One language for> the only other declarative language most people use. You might was > well be speaking English with Japanese grammar. the client, one for the db. Why not ONE language for both? > But wait a minute! The blocked_flag is redundant and the whole goal of Perhaps the most absurd statement of all:) Name me an sql db that DOESN'T> RDBMS is to get rid of data redundancies. support duplicates! I can name one:) This is like you chidding about a table that doesn't have a key. Well if doesn't have a key and it's in the db why does sql still consider it a table? > I tell people it takes at least a year of full-time SQL coding to have Untrue. You show that such an epiphany is non-deterministic:(> an epiphany and to throw out the old thought patterns. If you prefer Darwin to Roberston check out: http://racster.blogspot.com/ >> Really? What your suggesting to people is if they could write: If x=y then write 'HELLO!'where x and y are TABLES, this is to discouraged. Church of SQL speaks:) << This is fine (with a different set equality syntax) in a host language, but SQL has no I/O of its own. We used to joke that it meant "Scarcely Qualifies as a Language" in ANSI. The purp[ose of SQL is to return a set of rows that the host language can convert into the proper local data types. It is not a general purpose language. >> Impedence Mismatch. This is your classic client/server. One language for the client, one for the db. Why not ONE language for both? << We tried that for a few decades and it did not work. Data became boundto Fortran, COBOL, PL/I or whatever the language du jour happened to be and we spent a lot of time trying to move it around. Data is fundamentally diffrerent from procedures >> Perhaps the most absurd statement of all:) Name me an sql db that DOESN'T support duplicates! << Do you feel that the goal is to preserve or increase data redundancies?Now, that would be absurd. And, yes, SQL has some baggage from the old file systems upon which the first prototypes were built. But good SQL programmers write code that avoids the problem spots. >> [ 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] Untrue. You show that such an epiphany is non-deterministic:( << That is my observation from writing seven books, my time in ANSI andteaching SQL classes for a few decades. What is your experience? I might also add that some people never "get it" after many years. Remember Nietzche's Beyond Good and Evil? (And for the nitwits
that don't understand it's a metaphor for the 'individual'). You proscribe things that make a 'good' sql programmer vs. a 'bad' sql programmer. I'm suggesting we can go beyond the 'good' and 'bad' of sql. People are so intent on how to do something they have lost sight of why their doing it in the first place. So you get Sql Server, Oracle and the rest of the usual suspects being taught as both the chicken and the egg. If you could take sql and make it more logically rigorous, create an algebra for it, access it as in a 'imperative' language and put it in system that uses a 'declarative' approach to application development would you find this appealling? This has already been done. It's called Dataphor (www.alphora.com). Think of what it would mean to the industry if 'you' considered another 'it'. best, steve Dataphor and more @: http://racster.blogspot.com/ Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1156025185.399893.26620@i42g2000cwa.googlegroups.com... >>> Really? What your suggesting to people is if they could write: > > If x=y then write 'HELLO!' > > where x and y are TABLES, this is to discouraged. Church of SQL > speaks:) << > > This is fine (with a different set equality syntax) in a host language, > but SQL has no I/O of its own. We used to joke that it meant > "Scarcely Qualifies as a Language" in ANSI. The purp[ose of SQL is to > return a set of rows that the host language can convert into the proper > local data types. It is not a general purpose language. > >>> Impedence Mismatch. This is your classic client/server. One language for >>> the client, one for the db. Why not ONE language for both? << > > We tried that for a few decades and it did not work. Data became bound > to Fortran, COBOL, PL/I or whatever the language du jour happened to be > and we spent a lot of time trying to move it around. Data is > fundamentally diffrerent from procedures > >>> Perhaps the most absurd statement of all:) Name me an sql db that >>> DOESN'T support duplicates! << > > Do you feel that the goal is to preserve or increase data redundancies? > Now, that would be absurd. And, yes, SQL has some baggage from the old > file systems upon which the first prototypes were built. But good SQL > programmers write code that avoids the problem spots. > >>> [ 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] Untrue. You show >>> that such an epiphany is non-deterministic:( << > > That is my observation from writing seven books, my time in ANSI and > teaching SQL classes for a few decades. What is your experience? I > might also add that some people never "get it" after many years. >  |
|||||||||||||||||||||||
Other interesting topics