sql server - Function to return odd numbers within a range -

i trying make script inline value function cte want table shows odd numbers if input (1) shows 1,3,5,7,9,11 put n < 11 script shows every numbers 1 11. should add on ?

 create function [dbo].[oddnumfunction]  (  @oddnum int  )  returns table   return  r_table(n)    (  select @oddnum n  union  select n + 1 r_table  n < 11    )  select * r_table  

rather use recursive cte, go simpler:

declare @oddnum int = 1;  select number  master..spt_values  [type] = n'p'    , number % 2 = 1   , number between @oddnum , 11; 

another way, if have numbers table (which immensely useful). doesn't have contain 1,000,000 rows, demonstrate can. compression, takes 11 mb; without, 13 mb.

create table dbo.numbers(number int primary key)  (data_compression = page); -- recommended if edition supports  insert dbo.numbers(number) select top (1000000)    row_number() on (order s1.[object_id])    sys.all_objects s1    cross join sys.all_objects s2;  select number dbo.numbers; -- prime 

(and when use this, can create function with schemabinding, has additional benefits.)

now:

declare @oddnum int = 1;  select number   dbo.numbers   number % 2 = 1   , number between @oddnum , 11; 

so function be:

create function [dbo].[oddnumfunction2] (   @oddnum int ) returns table schemabinding   return   (     select number       dbo.numbers       number % 2 = 1       , number between @oddnum , 11   ); 

---

performance comparisons, running 10,000 times (and stuffing output #temp table):

gidil:                 30.31 seconds mahmoud:               29.11 seconds me (spt_values):       27.91 seconds me (numbers):          28.06 seconds 

the reason small spt_values table in memory (and forced numbers table be), , low number of logicals read required (slightly!) less expensive computation of recursive cte (even 1 produces @ 6 rows).

i surprised mahmoud's came out faster gidil's, ran multiple times , results consistent. feel try test them , compare. while in cases performance difference negligible, don't hand-wave these things away, , if i've found efficient way know something, rather use it, if runner-up right on heels.

---

if want cte, following handle odd numbers given input (odd or even) between 0 , 11:

declare @oddnum int = 1;  ;with n(n) (    select @oddnum + ((@oddnum-1)%2)   union   select n + 2 n n < 11 ) select n n;