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
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 The Forms of Things Unknown
 Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 7

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  14:50:21  Show Profile  Reply with Quote
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!
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/24/2005 :  15:03:56  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  15:09:16  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 02/24/2005 :  15:11:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
That's the point of a code generator; it generates code. You then have to execute it.

Tara
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/24/2005 :  15:13:30  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  15:19:06  Show Profile  Reply with Quote
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!
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 02/24/2005 :  15:25:54  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 02/24/2005 :  15:27:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  15:30:27  Show Profile  Reply with Quote
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!
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  15:34:43  Show Profile  Reply with Quote
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!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 02/24/2005 :  15:36:03  Show Profile  Visit spirit1's Homepage  Reply with Quote
or use xp_execresultset

Go with the flow & have fun! Else fight the flow
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 02/24/2005 :  15:36:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 02/24/2005 :  15:38:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/24/2005 :  15:39:51  Show Profile  Reply with Quote
>> 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
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  15:40:31  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 02/24/2005 :  15:40:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  15:42:10  Show Profile  Reply with Quote
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!
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
666 Posts

Posted - 02/24/2005 :  15:48:01  Show Profile  Reply with Quote
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!
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 02/24/2005 :  16:01:06  Show Profile  Reply with Quote
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.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/24/2005 :  16:14:38  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 7 Previous Topic Topic Next Topic  
Previous Page | Next Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000