leading zero and how to create temporary table

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 2008 Forums

Other SQL Server 2008 Topics

leading zero and how to create temporary table

Author

Topic

kabon
Starting Member

48 Posts

Posted - 2012-06-18 : 04:30:37

Excuse me,

I want to ask how to display like this using leading zero:
1000001
1000002
1000003
1000004
1000005
1000006
1000007
1000008
1000009
1000010
1000011

I have the code but can't show the answer like that, this is my code:

declare @startrange int, @ID int
set @startrange = 0

while @startrange <= 10
BEGIN

set @ID = '1' + RIGHT('00000000'+CONVERT(varchar,@startrange),10)
PRINT @ID
set @startrange = @startrange + 1
END
GO

Can you help me?

And last, I want to know how to create temporary table and how to use it?

thanks...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-18 : 04:38:33

[code]
declare @startrange int

select @startrange = 1

; with
rcte as
(
select ID = @startrange

union all

select ID = ID + 1
from rcte
where ID <= 10
)
select 100000000 + ID
from rcte
[/code]

KH
[spoiler]Time is always against us[/spoiler]

kabon
Starting Member

48 Posts

Posted - 2012-06-18 : 23:43:21

If I change where ID <= 110, why this code error?

how about using temporary table and how to use it to cleared this problem?

help me,please....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-19 : 00:00:46

it is a recursive CTE. The default value for MAXRECURSION is 100

Specify the maxrecursion value or 0 for no limit.


FROM    rcte
OPTION (MAXRECURSION 1000);

KH
[spoiler]Time is always against us[/spoiler]

kabon
Starting Member

48 Posts

Posted - 2012-06-19 : 02:35:19

ehm, it's error.
where I must place that code?

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'OPTION'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-19 : 02:42:44

quote:
Originally posted by kabon

ehm, it's error.
where I must place that code?

I have already shown that in my post on 06/19/2012 : 00:00:46

KH
[spoiler]Time is always against us[/spoiler]

kabon
Starting Member

48 Posts

Posted - 2012-06-19 : 23:14:22

I had replace select ID = ID + 1
from rcte
where ID <= 10

to select ID = ID + 1
FROM rcte
OPTION (MAXRECURSION 1000);
where ID <= 10

but it always appear error that incorrect near OPTION

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-19 : 23:19:04

[code]
declare @startrange int

select @startrange = 1

; with
rcte as
(
select ID = @startrange

union all

select ID = ID + 1
from rcte
where ID <= 110
)
select 100000000 + ID
from rcte
OPTION (MAXRECURSION 1000);
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources