sql server - SQL set-based range -

how can have sql repeat set-based operation arbitrary number of times without looping? how can have sql perform operation against range of numbers? i'm looking way set-based loop.

know can create small table integers in it, 1 1000 , use range operations within range.

for example, if had table make select find sum of numbers 100-200 this:

select sum(n) numbers n between 100 , 200 

any ideas? i'm kinda looking works t-sql platform okay.

[edit] have own solution using sql clr works great ms sql 2005 or 2008. see below.

i think short answer question use clauses generate own.

unfortunately, big names in databases don't have built-in queryable number-range pseudo-tables. or, more generally, easy pure-sql data generation features. personally, think huge failing, because if did possible move lot of code locked in procedural scripts (t-sql, pl/sql, etc.) pure-sql, has number of benefits performance , code complexity.

so anyway, sounds need in general sense ability generate data on fly.

oracle , t-sql both support clause can used this. work little differently in different dbms's, , ms calls them "common table expressions", similar in form. using these recursion, can generate sequence of numbers or text values easily. here might like...

in oracle sql:

with   digits  -- limit recursion using digits.     (select       level - 1 num           dual           level < 10     connect       num = (prior num) + 1),   numrange     (select       ones.num         + (tens.num * 10)         + (hundreds.num * 100)         num           digits ones       cross join         digits tens       cross join         digits hundreds           hundreds.num in (1, 2)) -- use clause restrict each digit needed. select   -- columns , operations   numrange   -- join other data if needed 

this admittedly quite verbose. oracle's recursion functionality limited. syntax clunky, it's not performant, , limited 500 (i think) nested levels. why chose use recursion first 10 digits, , cross (cartesian) joins combine them actual numbers.

i haven't used sql server's common table expressions myself, since allow self-reference, recursion simpler in oracle. whether performance comparable, , nesting limits are, don't know.

at rate, recursion , clause useful tools in creating queries require on-the-fly generated data sets. querying data set, doing operations on values, can sorts of different types of generated data. aggregations, duplications, combinations, permutations, , on. can use such generated data aid in rolling or drilling down other data.

update: want add that, once start working data in way, opens mind new ways of thinking sql. it's not scripting language. it's robust data-driven declarative language. it's pain use because years has suffered dearth of enhancements aid in reducing redundancy needed complex operations. nonetheless powerful, , intuitive way work data sets both target , driver of algorithms.