Author |
Topic  |
Xerxes
Aged Yak Warrior
USA
666 Posts |
Posted - 02/24/2005 : 14:50:21
|
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
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! |
 |
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 02/24/2005 : 15:03:56
|
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 |
Edited by - Seventhnight on 02/24/2005 15:05:09 |
 |
|
Xerxes
Aged Yak Warrior
USA
666 Posts |
Posted - 02/24/2005 : 15:09:16
|
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
Speaking of which, Corey, that routine that generated the 1 to 42 iterations of code won't execute. It generates the code. Am I missing something to kick it off? I put it in a stored procedure.
HELP! 
Semper fi,
XERXES, USMC(Ret.) ------------------------------------------------------ Once a Marine ALWAYS a Marine! |
 |
|
tkizer
Almighty SQL Goddess
USA
38200 Posts |
Posted - 02/24/2005 : 15:11:38
|
That's the point of a code generator; it generates code. You then have to execute it.
Tara |
 |
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 02/24/2005 : 15:13:30
|
hehehe... yeah any that 'code generator' only produces the code. It does not execute. Use it to generate the code once. (ex. 1 to 42) then put the generated code in the sproc. You may want to put the generator in the sproc, commented out of course, so you can regenerate at a later need.
Sorry for the confusion, I guess I forgot to mention that part 
Corey
 "If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain |
 |
|
Xerxes
Aged Yak Warrior
USA
666 Posts |
Posted - 02/24/2005 : 15:19:06
|
quote: Originally posted by tduggan
That's the point of a code generator; it generates code. You then have to execute it.
Tara
I hope everyone      had a great laugh on this one! And you're sure to enjoy this side-splitter, too ! But if I'm generating the code in a sproc then how would I execute it?
Feelin' like a Homer Simpson by th' minute! DOH!
Semper fi,
XERXES, USMC(Ret.) ------------------------------------------------------ Once a Marine ALWAYS a Marine! |
 |
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 02/24/2005 : 15:25:54
|
My 2 cents..
I have a numbers table in the model db.. I have a table valued function exactly like seventhnight's that returns a range from the physical table. I have a table valued function the generates a Calendar of dates from the numbers table.
Numbers... cannot live without them...
DavidM
A front-end is something that tries to violate a back-end. |
 |
|
tkizer
Almighty SQL Goddess
USA
38200 Posts |
Posted - 02/24/2005 : 15:27:48
|
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 |
 |
|
Xerxes
Aged Yak Warrior
USA
666 Posts |
Posted - 02/24/2005 : 15:30:27
|
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
Oh...sorta like generating another sproc inside the sproc that generated the code...did I get that right?
Semper fi,
XERXES, USMC(Ret.) ------------------------------------------------------ Once a Marine ALWAYS a Marine! |
 |
|
Xerxes
Aged Yak Warrior
USA
666 Posts |
Posted - 02/24/2005 : 15:34:43
|
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! |
 |
|
spirit1
Cybernetic Yak Master
Slovenia
11752 Posts |
Posted - 02/24/2005 : 15:36:03
|
or use xp_execresultset
Go with the flow & have fun! Else fight the flow  |
 |
|
tkizer
Almighty SQL Goddess
USA
38200 Posts |
Posted - 02/24/2005 : 15:36:39
|
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 |
Edited by - tkizer on 02/24/2005 15:37:12 |
 |
|
tkizer
Almighty SQL Goddess
USA
38200 Posts |
Posted - 02/24/2005 : 15:38:15
|
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!
It doesn't require user intervention. It requires the programmer to do it once. It does not require a user to ever do this.
Tara |
 |
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 02/24/2005 : 15:39:51
|
>> I have a numbers table in the model db.. Great idea!, wish I had thought of that...
This is what I am using now: (someone ever did a perf measure on diff numbers functions?) And thanks to David I just ran it in model :)
-- 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
rockmoose |
 |
|
Xerxes
Aged Yak Warrior
USA
666 Posts |
Posted - 02/24/2005 : 15:40:31
|
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
Yeah, I could have done that, but I thought it would be better to have it generate at execution time. It would be ideal maintenance-wise! Sorry if I misstated.
Thanks..
Semper fi,
XERXES, USMC(Ret.) ------------------------------------------------------ Once a Marine ALWAYS a Marine! |
 |
|
tkizer
Almighty SQL Goddess
USA
38200 Posts |
Posted - 02/24/2005 : 15:40:39
|
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 |
Edited by - tkizer on 02/24/2005 15:41:22 |
 |
|
Xerxes
Aged Yak Warrior
USA
666 Posts |
Posted - 02/24/2005 : 15:42:10
|
quote: Originally posted by spirit1
or use xp_execresultset
Go with the flow & have fun! Else fight the flow 
Thanks, Mladen!
Semper fi,
XERXES, USMC(Ret.) ------------------------------------------------------ Once a Marine ALWAYS a Marine! |
 |
|
Xerxes
Aged Yak Warrior
USA
666 Posts |
Posted - 02/24/2005 : 15:48:01
|
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
Point taken, Tara . It's not feasible for an applictaion. I oughta know better. Shame on me . This is Q&D stuff. I'll do the practical, logical thing: I'll just put the code itself in the sproc! THANKS FOR ALL YOUR HELP!!! 
I'll be a decent Yak, yet!
Semper fi,
XERXES, USMC(Ret.) ------------------------------------------------------ Once a Marine ALWAYS a Marine! |
 |
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 02/24/2005 : 16:01:06
|
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?
DavidM
A front-end is something that tries to violate a back-end. |
 |
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 02/24/2005 : 16:14:38
|
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?
I have smallint in the number table, but the function returns int because of the 32k issue. I "always" use 8k rows in physical number table fixed. I have no particular reason, and You might be right about the int under the cover, I don't know. Well the savings in storage are laughable anyway...
EDIT: Actually I think I am suffering from typeconversion, int seems faster!
rockmoose |
Edited by - rockmoose on 02/24/2005 16:26:35 |
 |
|
Topic  |
|
|
|