quote:

---

Originally posted by Seventhnight

quote:

---

Originally posted by spirit1

arghhhhh ..... i was hoping you'd come up with something that has no loop.
i was playing with it and can't find a way to do that with no loop...

Go with the flow & have fun! Else fight the flow

---

Because you complained
granted generating the plan takes a few seconds.... once done its fine

Declare @beginN int,
	@EndN int,
	@Step int

Select 
	@BeginN = -14,
	@EndN = 53,
	@step = 3

Declare @s table (n int)

Insert Into @s
Select n = @beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14)*@step
From (Select * From (Select n1=0 Union All Select power(2,0)) A Where n1<=abs((@BeginN - @EndN)/@step)) n1
Cross Join (Select * From (Select n2=0 Union All Select power(2,1)) A Where n2<=abs((@BeginN - @EndN)/@step)) n2
Cross Join (Select * From (Select n3=0 Union All Select power(2,2)) A Where n3<=abs((@BeginN - @EndN)/@step)) n3
Cross Join (Select * From (Select n4=0 Union All Select power(2,3)) A Where n4<=abs((@BeginN - @EndN)/@step)) n4
Cross Join (Select * From (Select n5=0 Union All Select power(2,4)) A Where n5<=abs((@BeginN - @EndN)/@step)) n5
Cross Join (Select * From (Select n6=0 Union All Select power(2,5)) A Where n6<=abs((@BeginN - @EndN)/@step)) n6
Cross Join (Select * From (Select n7=0 Union All Select power(2,6)) A Where n7<=abs((@BeginN - @EndN)/@step)) n7
Cross Join (Select * From (Select n8=0 Union All Select power(2,7)) A Where n8<=abs((@BeginN - @EndN)/@step)) n8
Cross Join (Select * From (Select n9=0 Union All Select power(2,8)) A Where n9<=abs((@BeginN - @EndN)/@step)) n9
Cross Join (Select * From (Select n10=0 Union All Select power(2,9)) A Where n10<=abs((@BeginN - @EndN)/@step)) n10
Cross Join (Select * From (Select n11=0 Union All Select power(2,10)) A Where n11<=abs((@BeginN - @EndN)/@step)) n11
Cross Join (Select * From (Select n12=0 Union All Select power(2,11)) A Where n12<=abs((@BeginN - @EndN)/@step)) n12
Cross Join (Select * From (Select n13=0 Union All Select power(2,12)) A Where n13<=abs((@BeginN - @EndN)/@step)) n13
Cross Join (Select * From (Select n14=0 Union All Select power(2,13)) A Where n14<=abs((@BeginN - @EndN)/@step)) n14
Where @beginN+(n1+n2+n3+n4+n5+n6+n7)*@step < @EndN
Order By n

Select * from @s

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

---

quote:

---

Originally posted by Xerxes

...
Y'know, Corey, that LOOP looks a whole lot better than that 'book' you wrote!

Semper fi,

Xerxes, USMC(Ret.)
------------------------------------------------------
Once a Marine ALWAYS a Marine!

---

Declare @beginN int,
	@EndN int,
	@Step int

Select 
	@BeginN = -14,
	@EndN = 53,
	@step = 3

Declare @s table (n int)

Insert Into @s
Select n=@beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14+n15+n16+n17+n18)*@step From
(Select * From (Select n1=0 Union All Select power(2,0)) A, 
(Select * From (Select n2=0 Union All Select power(2,1)) A, 
(Select * From (Select n3=0 Union All Select power(2,2)) A, 
(Select * From (Select n4=0 Union All Select power(2,3)) A, 
(Select * From (Select n5=0 Union All Select power(2,4)) A, 
(Select * From (Select n6=0 Union All Select power(2,5)) A, 
(Select * From (Select n7=0 Union All Select power(2,6)) A, 
(Select * From (Select n8=0 Union All Select power(2,7)) A, 
(Select * From (Select n9=0 Union All Select power(2,8)) A, 
(Select * From (Select n10=0 Union All Select power(2,9)) A, 
(Select * From (Select n11=0 Union All Select power(2,10)) A, 
(Select * From (Select n12=0 Union All Select power(2,11)) A, 
(Select * From (Select n13=0 Union All Select power(2,12)) A, 
(Select * From (Select n14=0 Union All Select power(2,13)) A, 
(Select * From (Select n15=0 Union All Select power(2,14)) A, 
(Select * From (Select n16=0 Union All Select power(2,15)) A, 
(Select * From (Select n17=0 Union All Select power(2,16)) A, 
(Select * From (Select n18=0 Union All Select power(2,17)) A 
Where n18<=abs((@BeginN - @EndN)/@step)) n18 
Where n17<=abs((@BeginN - @EndN)/@step)) n17
Where n16<=abs((@BeginN - @EndN)/@step)) n16
Where n15<=abs((@BeginN - @EndN)/@step)) n15
Where n14<=abs((@BeginN - @EndN)/@step)) n14
Where n13<=abs((@BeginN - @EndN)/@step)) n13
Where n12<=abs((@BeginN - @EndN)/@step)) n12
Where n11<=abs((@BeginN - @EndN)/@step)) n11
Where n10<=abs((@BeginN - @EndN)/@step)) n10
Where n9<=abs((@BeginN - @EndN)/@step)) n9
Where n8<=abs((@BeginN - @EndN)/@step)) n8
Where n7<=abs((@BeginN - @EndN)/@step)) n7
Where n6<=abs((@BeginN - @EndN)/@step)) n6
Where n5<=abs((@BeginN - @EndN)/@step)) n5
Where n4<=abs((@BeginN - @EndN)/@step)) n4
Where n3<=abs((@BeginN - @EndN)/@step)) n3
Where n2<=abs((@BeginN - @EndN)/@step)) n2
Where n1<=abs((@BeginN - @EndN)/@step)) n1
Where @EndN >= @beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14+n15+n16+n17+n18)*@step
Order By n

Select * from @s

quote:

---

Originally posted by Seventhnight

quote:

---

Originally posted by Xerxes

...
Y'know, Corey, that LOOP looks a whole lot better than that 'book' you wrote!

Semper fi,

Xerxes, USMC(Ret.)
------------------------------------------------------
Once a Marine ALWAYS a Marine!

---

If you didn't like that one, I have another. This one actually seems to perform better Looks worse though

Declare @beginN int,
	@EndN int,
	@Step int

Select 
	@BeginN = -14,
	@EndN = 53,
	@step = 3

Declare @s table (n int)

Insert Into @s
Select n=@beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14+n15+n16+n17+n18)*@step From
(Select * From (Select n1=0 Union All Select power(2,0)) A, 
(Select * From (Select n2=0 Union All Select power(2,1)) A, 
(Select * From (Select n3=0 Union All Select power(2,2)) A, 
(Select * From (Select n4=0 Union All Select power(2,3)) A, 
(Select * From (Select n5=0 Union All Select power(2,4)) A, 
(Select * From (Select n6=0 Union All Select power(2,5)) A, 
(Select * From (Select n7=0 Union All Select power(2,6)) A, 
(Select * From (Select n8=0 Union All Select power(2,7)) A, 
(Select * From (Select n9=0 Union All Select power(2,8)) A, 
(Select * From (Select n10=0 Union All Select power(2,9)) A, 
(Select * From (Select n11=0 Union All Select power(2,10)) A, 
(Select * From (Select n12=0 Union All Select power(2,11)) A, 
(Select * From (Select n13=0 Union All Select power(2,12)) A, 
(Select * From (Select n14=0 Union All Select power(2,13)) A, 
(Select * From (Select n15=0 Union All Select power(2,14)) A, 
(Select * From (Select n16=0 Union All Select power(2,15)) A, 
(Select * From (Select n17=0 Union All Select power(2,16)) A, 
(Select * From (Select n18=0 Union All Select power(2,17)) A 
Where n18<=abs((@BeginN - @EndN)/@step)) n18 
Where n17<=abs((@BeginN - @EndN)/@step)) n17
Where n16<=abs((@BeginN - @EndN)/@step)) n16
Where n15<=abs((@BeginN - @EndN)/@step)) n15
Where n14<=abs((@BeginN - @EndN)/@step)) n14
Where n13<=abs((@BeginN - @EndN)/@step)) n13
Where n12<=abs((@BeginN - @EndN)/@step)) n12
Where n11<=abs((@BeginN - @EndN)/@step)) n11
Where n10<=abs((@BeginN - @EndN)/@step)) n10
Where n9<=abs((@BeginN - @EndN)/@step)) n9
Where n8<=abs((@BeginN - @EndN)/@step)) n8
Where n7<=abs((@BeginN - @EndN)/@step)) n7
Where n6<=abs((@BeginN - @EndN)/@step)) n6
Where n5<=abs((@BeginN - @EndN)/@step)) n5
Where n4<=abs((@BeginN - @EndN)/@step)) n4
Where n3<=abs((@BeginN - @EndN)/@step)) n3
Where n2<=abs((@BeginN - @EndN)/@step)) n2
Where n1<=abs((@BeginN - @EndN)/@step)) n1
Where @EndN >= @beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14+n15+n16+n17+n18)*@step
Order By n

Select * from @s

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

---

quote:

---

Originally posted by tduggan

That's the point of a code generator; it generates code. You then have to execute it.

Tara

---

quote:

---

Originally posted by tduggan

You execute it by copying the generated code into a Query Analyzer window and running it. Or putting it inside a stored proc, then executing that stored proc.

Tara

---

quote:

---

Originally posted by Xerxes

My dilemma is: This is going to be part of a process that will involve NO user interface. I have to make that code-generated process transparent. And that code must execute WITHOUT any user-interface.

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
Once a Marine ALWAYS a Marine!

---

-- generate number table
set nocount on
create table number(n smallint identity(0,1) primary key clustered check(n between 0 and 7999))
deny delete on number to public
while 7999 > (select coalesce(max(n),0) from number)
	insert number default values
GO

create  function dbo.fnSequence(@n int,@base int) returns @number table(n int primary key clustered)
with schemabinding 
as
/*	generates sequence from 0 based number table
	WORD OF WARNING: the maximum sequence is count(*)^2 from number table
	usage:
	select * from dbo.fnSequence(10000,-5000) -- generate 10000 numbers from base -5000 */
begin
	-- does number table have large enough sequence already
	if @n <= (select count(*) from dbo.number)
		insert @number select @base+n from dbo.number where n between 0 and @n-1
	else	-- generate sequence
		insert @number
		select
			@base+p1.n+(p2.n)*ceiling(sqrt(@n))
		from
			dbo.number p1 join dbo.number p2
				on p1.n < ceiling(sqrt(@n))
				and p2.n < ceiling(sqrt(@n))
		where
			p1.n+(p2.n)*ceiling(sqrt(@n)) < @n
	return
end
GO

select * from dbo.fnSequence(10000,-10)
--drop function dbo.fnSequence
--drop table number

quote:

---

Originally posted by tduggan

Well you don't put Corey's code in a sproc though. You just run it once inside Query Analyzer to generate your code. Then you copy that generated code and put that into your sproc. It saves you from the redundant typing that you would have to do to create these INSERT statements. As Corey suggested, copy his code into your sproc but comment it out just so that you have it handy in there.

Tara

---

quote:

---

Originally posted by spirit1

or use xp_execresultset

Go with the flow & have fun! Else fight the flow

---

quote:

---

Originally posted by spirit1

or use xp_execresultset

Go with the flow & have fun! Else fight the flow

---

quote:

---

Originally posted by tduggan

quote:

---

Originally posted by spirit1

or use xp_execresultset

Go with the flow & have fun! Else fight the flow

---

I personally don't think that's a solution for application code. That should only be used for ad-hoc type stuff or DBA routines. I'm of the opinion that applications should not be executing extended stored procedures except where absolutely necessary. In this case, it isn't necessary as Xerxes just needs to do a copy and paste once.

Tara

---

quote:

---

Originally posted by byrmol

Rockmoose,

I noticed you use a smallint data type.. Any reason for this besides the obvious? I ask because I use an int and have one stage exceeded the 32K rows in the table.

And I have always assumed (myth number 14) that SQL Server would convert smallint to int for arithmetic.
Any myth busters out there who know otherwise?

---