Thought game: What if SQL had a type called 'Operator'

'Don2' on Fri, 08 Feb 2008 10:13:51 GMT, sez:

in C# you'd use anonymous functions to sort this problem out.

you'd have a delegate of type predicate and simply pass that in.

'Marco P' on Fri, 08 Feb 2008 10:18:00 GMT, sez:

What's so bad about Dynamic Sql?

Of course there's the 'threat' of Sql Injection attacks, but that can be circumvented with careful parsing of the input.

Make sure '@Operator' which is of type VarChar(2) maybe, , is equal to a set of possible values, for example: "<", "<=", "<>", "!=", "=", "==", ">=" and if it is not raise an error.

Just like raising an invalid operation exception.

Maybe it can be a user defined type, so that you don't need to check this each time?

And then construct the query dynamically from the given elements. It would be safe if the value of "@Operator" is limited correctly.

'JJ' on Fri, 08 Feb 2008 10:24:47 GMT, sez:

let me guess:
this is a problem you ran into this week and rather than finding a way to modularise the code you implemented it as two separate stored procedures?
for shame!
JJ
p.s. still astroboy?

'RJ' on Fri, 08 Feb 2008 10:27:07 GMT, sez:

Whilst we are busy fixing SQL, can we also add
UNLESS EXISTS(@field) so that I can do

INSERT INTO Table(Id,Name) VALUES(...) UNLESS EXISTS(Name)

'Cisco Routiero' on Fri, 08 Feb 2008 10:35:03 GMT, sez:

@RJ,
>Whilst we are busy fixing SQL

And why not move the FROM before the SELECT so we get more intuitive code and better intellisense from our tools.

we need intellisense no because we are stupid but because we are stuck editing someone elses schema that we do not know the ins and the outs from and their column names are new to us but we understand them when we see them

SQL 92 was a very long time ago, i was 4 year old and now i am senior technical!!!

'g' on Fri, 08 Feb 2008 10:58:24 GMT, sez:

Use an "if" clause to avoid dynamic sql and pass the variable as a parameter.

if (@operation = '<'
select foo from bar where age < @age
else
select foo from bar where age > @age

'Marcos' on Fri, 08 Feb 2008 10:59:34 GMT, sez:

Hi there Leon =)

For filtering or query conditions is better to keep away from stored procedures and use direct SQL generated with NHibernate, or something like that.

But in some stored procedures you can use:

SELECT *
FROM People
WHERE
(@Operator = '>' AND Age > @Age)
OR
(@Operator = '<=' AND Age <= @Age)

Anyway this is SLOW and UGLY =) lol

Better to create a class like a FilterBuilder with methods: GraterThan, Like, Between and let it create the where condition =)

Cheers

'Don2' on Fri, 08 Feb 2008 11:02:58 GMT, sez:

So what you really want is Object-Oriented Queries, but within a domain specific dialect of SQL.

More than just operator types, you want inheritance, polymorphism and so on, with a syntax that matches the domain, and doesn't require a complete OO knowledge/capability.

You could say:

EXEC @Query WITH -- a variable of type query,
FROM &=
INNER JOIN business ON
Person.BusinessID = business.BusinessID
SELECT &=
business.Name
WHERE &=
business.Bankrupt = False



But the important thing with OO is the ability to see exactly what is inheriting from where

So also you need some kind of trace tool that will show you how a statement expands out.

Okay my mind is over taxed already

'lb' on Fri, 08 Feb 2008 11:14:09 GMT, sez:

@g, @marcos

i agree that your solutions are practical and probably the right approach.

but i like to dream.

think of sql as this domain specific language perfectly suited for set based rdbms operations -- unlike C#, and ignoring the very singular-based if statement.

it ought to evolve to handle all the cases it requires, just as all living languages evolve.

lb

'Marco P' on Fri, 08 Feb 2008 11:28:31 GMT, sez:

@g

i dont agree about using an if clause because this means a redundant copy of the entire sql statemnt which in practice would be much bigger, at least 20 times.

'Daniel Pollock' on Fri, 08 Feb 2008 11:32:22 GMT, sez:

My first thought was case statements. But that still doesn't simplify the syntax one bit. You still would have to compare the condition to some arbitrary string (i,e, where case @op = '>' then >).

I see something like using a special symbol to denote builtin operators. Something like $op and have that as type in sql server.

So the syntax would be like what you suggest at first, but instead of using the @ which would get confused with other variables, we would use $ (or something else) to denote a builtin operation type.

'Marcos' on Fri, 08 Feb 2008 11:33:44 GMT, sez:

@Leon

I agree with u, would be really cool to get an operator type =) and declare vars of that type and later use them everywhere an operator is needed.

I don't think that it gets so hard to implement for the RDBMS =)

I was using some kind of if like 'g' propose but, you need to repeat all the SELECT statement and I always forget to change all later :P

Cheers

'anon' on Fri, 08 Feb 2008 11:38:23 GMT, sez:

@JJ:
>this is a problem you ran into this week
>and rather than finding a way to modularise
>the code you implemented it as two separate
>stored procedures?

or maybe:

this is a problem you ran into Just Now and rather than think about it, you thought you'd ask your readers.

Well played!

'Don2' on Fri, 08 Feb 2008 11:41:59 GMT, sez:

@Daniel P:

>special symbol to denote builtin operators

this is like how C# uses angle brackets to denote type parameters, i.e. 'generics'.

I think it's strange to indicate these parameters as being so different to other parameters.

I'd rather the syntax was consistent, just the type was different (Var @Operator as Operator for example).

But maybe I don't understand the implications

'dysfunctor' on Fri, 08 Feb 2008 12:28:30 GMT, sez:

Congratulations, Leon. You've just (re)invented functional programming. :-)

'Chris Ammerman' on Fri, 08 Feb 2008 13:00:06 GMT, sez:

@dysfunctor:

It wasn't much of a leap considering SQL is already a fairly sophisticated declarative language.

In all seriousness though, if SQL could be made into a fully-qualified functional language, many complex queries would instantly be made less verbose and less repetitive.

It's ridiculous, for example, that I can't define a query-scoped function or something for a calculated field to avoid having to either retype the whole calculation everywhere I need to use the result, or using sub-queries to circumvent the inconvenience (at a cost of performance). Essentially a LET clause would be a godsend.

'Matthew Martin' on Fri, 08 Feb 2008 13:55:26 GMT, sez:

In response to the earlier comment/query sample that was said to be using OR operators is slow.
select age from people where @op='gt' and @age>age
union
select age from people where @op='eq' and @age=age
union
etc... Results should run quick-- no OR's.

'John' on Fri, 08 Feb 2008 14:37:06 GMT, sez:

Hmm, an interesting concept. But trying to not think like a programmer for a minute but think like a DBA instead:
How would the SQL engine would generate a query plan for such a beast?

'Ben' on Fri, 08 Feb 2008 17:47:57 GMT, sez:

I agree! There should exist enough things like this to rid us of dynamic sql.

'haacked' on Fri, 08 Feb 2008 17:54:34 GMT, sez:

I'd just use a multiplier...

SELECT *
FROM People
WHERE @multiplier * Age < @ multiplier * @Age

Pass in 1 for Age < @Age and -1 for Age > @Age. Done, no need to add @operator.

Phil

'Eber Irigoyen' on Sat, 09 Feb 2008 16:45:19 GMT, sez:

@haacked, I would have to run some tests, but I don't think that's a good idea

'Atli Oddsson' on Sat, 09 Feb 2008 17:55:48 GMT, sez:

@haacked: this would probably cause a table scan, and the table contains 6 billion rows!

anyhow, to solve this *particular* problem I would pass in @ageFrom and @ageTo since you obviously know the max and the min and formulate the query like this:

SELECT *
FROM People
WHERE Age between @agefrom AND @ageTo

Of course it isn't a generic solution. Code duplication is a real pain in SQL!

'Tim Wintle' on Thu, 14 Feb 2008 15:32:40 GMT, sez:

A very good idea, but I don't think it could be implemented very efficiently within the engine.

My favourite general suggestion is Matthew Martin's - I think it should be fairly efficient with the proper indexes - but it depends on your usage:

clearly if you are going to have a single operator for a query then it would be more efficient to do (protected) conditional statements in your calling code (assuming your main worry is the query time and hanging up the database).

If you are doing it on a per-row basis, then I think that using union must be the best option, as you retain the ability to use your indexes.

'Andrew' on Sun, 09 Mar 2008 19:06:23 GMT, sez:

So you want call the procedure like getPeopleByAge(14,'>');
Why not extend it? Modularise the operation (now it's not flexible SELECT), table (well, there are not only people having age, I want to get info on my equipment too!) and filter field.

Now we would have getByCriteria('People','Age','>',14);

Oh wait.

name


website (optional)


enter the word:
 

comment (HTML not allowed)

All viewpoints welcome. But the right to delete any post for any reason is reserved. Don't make me do it. Comments may be republished, emailed to your loved ones or printed and used as toilet paper. Who reads this legal bit anyhow?

TimeSnapper_{Know thyself!}

TimeSnapper is a life analysis system that stores and plays-back your computer use. It makes timesheet recording a breeze, helps you recover lost work and shows you how to sharpen your act.

TimeSnapper won last year's Developer Competition at Larkware.com, and is used by over 10,000 people.