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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ
 All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  The Forms of Things Unknown Reply to Topic  Printer Friendly
Author  Topic
Page: of 7

Xerxes
Aged Yak Warrior

USA
666 Posts

 Posted - 02/24/2005 :  14:50:21 quote:Originally posted by Seventhnightquote:Originally posted by spirit1arghhhhh ..... 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 TwainY'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 Seventhnightquote: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 TwainSpeaking 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 tdugganThat's the point of a code generator; it generates code. You then have to execute it.TaraI 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...DavidMA 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 tdugganYou 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.TaraOh...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_execresultsetGo 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 XerxesMy 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 tdugganWell 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.TaraYeah, 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 spirit1or use xp_execresultsetGo 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 spirit1or use xp_execresultsetGo 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 tdugganquote:Originally posted by spirit1or use xp_execresultsetGo 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.TaraPoint 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?DavidMA 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 byrmolRockmoose,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
Page: of 7  Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC