Print Numbers with out using LOOP

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)

Print Numbers with out using LOOP

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-07-01 : 08:37:05

hi,

In sql server 2005 is it possible to display numbers from 1 to 50 with out using any loops..if yes please advise..

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-01 : 08:39:07

select number
from master.dbo.spt_values
where type = 'p' and number between 1 and 50

Jim

Everyday I learn something that somebody else already knew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-01 : 08:39:46

SELECT number from master..spt_values where type = 'p' and number between 1 and 50

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-01 : 08:40:05

N 56°04'39.26"
E 12°55'05.63"

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-07-01 : 08:47:13

If the numbers to diplay is small then it will work..If i want display large numbers say 1000000 then its won't work?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-01 : 08:59:24

Please ask the exact question you want answered. 1 to 50 is a different question than 1 to 1000000

Jim

Everyday I learn something that somebody else already knew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-01 : 09:27:23

Create this funtion and make sure you remember you have the function.
It accepts two parameters and teh total range is same 2 billion or something in that neighbourhood.

SELECT Number FROM dbo.GetNumbers(1, 50)
SELECT Number FROM dbo.GetNumbers(1, 1000000)
SELECT Number FROM dbo.GetNumbers(50000, 75000)

CREATE FUNCTION [dbo].[GetNumbers]
(
	@FromNum INT,
	@ToNum INT
)
RETURNS TABLE
AS
RETURN
	WITH	n0 AS (SELECT 1 AS p UNION ALL SELECT 1),
		n1 AS (SELECT 1 AS p FROM n0 AS a CROSS JOIN n0 AS b),
		n2 AS (SELECT 1 AS p FROM n1 AS a CROSS JOIN n1 AS b),
		n3 AS (SELECT 1 AS p FROM n2 AS a CROSS JOIN n2 AS b),
		n4 AS (SELECT 1 AS p FROM n3 AS a CROSS JOIN n3 AS b),
		n5 AS (SELECT 1 AS p FROM n4 AS a CROSS JOIN n4 AS b)

	SELECT	@FromNum + n - 1 AS Number
	FROM	(
			SELECT	TOP (@ToNum - @FromNum + 1)
				ROW_NUMBER() OVER (ORDER BY p) AS n
			FROM	n5
		) AS d

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-04 : 10:30:24

or

select top 50 row_number() over (order by (select 1)) as sno from syscolumns

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources