Help me avoid a temp table or cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help me avoid a temp table or cursor

Author

Topic

F_Alihassan
Starting Member

3 Posts

Posted - 2003-09-03 : 05:23:32

How can I create a select statement that generates a result set composed of one column with values that start with a given parameter and the rest of the rows are increments of another parameter
e.x.

@startValue=5,@Increment=2
Resulting set:
Values
------
5
7
9
11
etc...

Thanks To U In Advance

mr_mist
Grunnio

1870 Posts

Posted - 2003-09-03 : 06:47:42

How big is your results set? Have you considered exporting the whole set to excel after the startvalue then getting excel to sort out the increments?

-------
Moo. :)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-03 : 06:55:37

see the WHILE statement construct....via a sample below.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27915

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-03 : 06:58:12

This is nr's solution (topic id=25685) with my minor adding (in bold):

declare @i int, @j int set @i=5 set @j=2

select z from
(select z=a+b+c+d+e+f+g+h+i+j+k+l+m+n
from
(select a=0 union select 1) as a ,
(select b=0 union select 2) as b ,
(select c=0 union select 4) as c ,
(select d=0 union select 8) as d ,
(select e=0 union select 16) as e ,
(select f=0 union select 32) as f ,
(select g=0 union select 64) as g ,
(select h=0 union select 128) as h ,
(select i=0 union select 256) as i ,
(select j=0 union select 512) as j ,
(select k=0 union select 1024) as k,
(select l=0 union select 2048) as l ,
(select m=0 union select 4096) as m ,
(select n=0 union select 8192) as n
) as a
where z < 1000

and z>=@i and (z-@i) % @j=0

order by z

F_Alihassan
Starting Member

3 Posts

Posted - 2003-09-03 : 07:11:09

Thank u you stoud, although this could do the trick, but it is not what I had in mind. Your solution is a bit static,
I was looking for a dynamic solution using the introduced parameters

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-03 : 07:21:06

Better (and what do you mean by 'static'????????????????):

declare @i int, @j int set @i=5 set @j=2

select z+@i as zz from
(select z=a+b+c+d+e+f+g+h+i+j+k+l+m+n
from
(select a=0 union select 1) as a ,
(select b=0 union select 2) as b ,
(select c=0 union select 4) as c ,
(select d=0 union select 8) as d ,
(select e=0 union select 16) as e ,
(select f=0 union select 32) as f ,
(select g=0 union select 64) as g ,
(select h=0 union select 128) as h ,
(select i=0 union select 256) as i ,
(select j=0 union select 512) as j ,
(select k=0 union select 1024) as k,
(select l=0 union select 2048) as l ,
(select m=0 union select 4096) as m ,
(select n=0 union select 8192) as n
) as a
where z < 100000

and z % @j=0

order by z

F_Alihassan
Starting Member

3 Posts

Posted - 2003-09-03 : 07:36:06

Dear Stoad
what I meant by static is that in your code you hard-coded the union statements and if you needed larger result set u need to further hard-coded add a new union statement. I think you way is the best available way there is for this matter.
Thank you for your reply

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-03 : 07:41:04

just create a table of 1000 numbers (1-1000), store it permanently in your database, and use that table. they are extremely useful. you can cross join the table to itself to get as many numbers as you need.

again, don't create the table of numbers each time the procedure is called, create it once at design time and leave it there.

then you can do:


SELECT @start + (N.Number-1) * @increment as Value
FROM 
 (SELECT N1.Number + (N2.Number-1) * 1000 as Number
  FROM Numbers N1 CROSS JOIN Numbers N2) N

Note that the above should be much more efficient and return a much greater range of numbers than using the % operator to filter out numbers that you don't want.

HOw many values should be returned ? is there an upper limit ?

And do you see that even with the union idea, if you allow the UNION to generate as many numbers as you will ever need (easier if you re-write it without the %), you will NOT have to edit the UNION query each time? just set it up once and never touch it again?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources