May 9, 2003 at 12:54 am
Hi,
I do need the help from all of you. Sure hope that i can get some help here.
My table is designed: -
DO_NO_ALPHAchar (1),
START_DO_RANGEint,
END_DO_RANGEint,
And the records are stored as such: -
DO_NO_ALPHA START_DO_RANGE END_DO_RANGE
----------- -------------- ------------
X 1 350
I have to create a table, which will look like this after reading the records above: -
DO_NO Status
------ ------
X00001 N
X00002 N
...... ...
... .......
...........
X00350 N
Status will always be 'N'
How should i write the Store procedure? Like say, if i want to write out a Store procedure that create a table temporary. After processing, the table will be dropped off.
Will it be the same if i would to write a procedure that creates the table permanently?
What will be the differences?
Please enlighten me.
Thank you and best regards.
May 9, 2003 at 3:55 am
Try this:
Drop Table #Temp0
Create table #Temp0(
DO_NO_ALPHA char (1),
START_DO_RANGE int,
END_DO_RANGE int
)
Insert into #Temp0 values('x', 1, 350)
Insert into #Temp0 values('y', 1, 25)
--=== using the method recently posted to get possibly numbers
Drop table #numbers
Create Table #numbers(
Digit int
)
Insert into #numbers select 0
Insert into #numbers select 1
Insert into #numbers select 2
Insert into #numbers select 3
Insert into #numbers select 4
Insert into #numbers select 5
Insert into #numbers select 6
Insert into #numbers select 7
Insert into #numbers select 8
Insert into #numbers select 9
Select
DO_NO = DO_NO_ALPHA +
Replicate( '0', 5-Len( (x1.Digit*1 + x2.digit*10 + x3.digit*100) ) )+
Convert(varchar(5), (x1.Digit*1 + x2.digit*10 + x3.digit*100) )
,Status = 'N'
From #Temp0 as t
Inner Join
(
#numbers as x1
Cross Join #numbers as x2
Cross Join #numbers as x3
)
on t.START_DO_RANGE <= (x1.Digit*1 + x2.digit*10 + x3.digit*100)
And t.END_DO_RANGE >= (x1.Digit*1 + x2.digit*10 + x3.digit*100)
order by
DO_NO_ALPHA, x1.Digit*1 + x2.digit*10 + x3.digit*100
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply