Fill Series in SQL

[princekool]

Hi guys;
I have a table of books in my DB and each page has a different serial no following each other eg. Page 9 has serial 005065 and 10 has 005066.
In my table it only gives me a range of serials as per the book, this is how my table looks like:

Book     1st_Serial       Last_Serial         No.of pages
1             00530901           00531000        100
2             006001              006025            25

I want to display all the serial nos. per book.
Can somebody plz help me with a hint of how i can query down to get the results i need?? Any auto-fill function I can use?

Your help will be appreciated. TQ

[princekool]

Any luck with this? Am still stuck :-(

[apegram]

I don't believe there's a simple single-query method of constructing your list. You can accomplish it using a programming language of your choice, or you can use a procedure in SQL Server to produce your output.

The below is not going to translate exactly for you, but it should give you the first step. I constructed my own temporary table to approximate yours, but the field names are different and datatypes are what they need to be to make the following code work. You can, of course, cast your field values to the appropriate types.

declare @bookid int
declare @firstSerial int
declare @lastSerial int

set @bookid = 1

select @firstSerial = firstSerial, @lastSerial = lastSerial from books where bookid = @bookid

declare @currentSerial int
set @currentSerial = @firstSerial

declare @bookserials table
(
	BookID	int,
	Serial	int
)

While @currentSerial <= @lastSerial
BEGIN
	Insert @bookserials (BookID, Serial) values (@bookid, @currentSerial)
	set @currentSerial = @currentSerial + 1
END

Select * from @bookserials