Please enjoy and feel free to adjust to your specific needs!
If you find a way to optimize the function further, just reply with your code fix.


CREATE FUNCTION dbo.fnSeqNumbers
(
@LowLimit INT = 1,
@HighLimit INT
)
RETURNS @Values TABLE
(
Value INT
)
AS

BEGIN
DECLARE @Temp INT

IF @LowLimit > @HighLimit
SELECT @Temp = @LowLimit,
@LowLimit = @HighLimit,
@HighLimit = @Temp

INSERT @Values VALUES (@LowLimit)

WHILE @@ROWCOUNT > 0
INSERT @Values
SELECT n.Value + t.Items
FROM @Values n
CROSS JOIN (
SELECT COUNT(*) Items
FROM @Values
) t
WHERE n.Value + t.Items <= @HighLimit

RETURN
END

I don't follow the purpose of the code sorry....

This is the basics of the function.

First I store 1 value (@LowLimit). Then I select this value, add it with the number of already stored values (1), add (@LowLimit + 1), and save.

Next iteration, I select the two already stored values (@LowLimit and @LowLimit + 1), increment them with the numbers already stored (2) and save the new values (@LowLimit + 2 and @LowLimit + 3).
And so on, so on... Until the @LowLimit reaches @HighLimit.

First insert, 1 record.
Second insert, 2 records.
Third insert, 4 records.
Fourth insert, 8 records.
Fifth insert, 16 records.
Sixth insert, 32 records.
Seventh insert, 64 records.
Eight insert, 128 records.
Ninth insert, 256 records.
Tenth insert, 512 records.
Eleventh insert, 1024 records.
Twelvth insert, 2048 records.

Until I get all the numbers I want.

The function come in handy in many several scenarios. One, for example is when you want to un-aggregate a table.

Name Items
-------- -----
Cucumber 3
Tomato 5

And You want the result as

Cucumber
Cucumber
Cucumber
Tomato
Tomato
Tomato
Tomato
Tomato

Use this query!

declare @max int

select @max = max(Items) from table1

SELECT T.Item, X.Value
FROM #Temp T
INNER JOIN (SELECT Value FROM dbo.fnSeqNumbers(1, @max)) X ON T.Items >= X.Value

Then the table is un-aggregated and even ordered!




Today I was asked to produce a graph containing currency differences in euros. A typical BI solution.

I just created a temp table and populated it with

INSERT INTO #CurrDiff
SELECT Value FROM dbo.fnSeqNumbers(-1000, 1000)

Then I joined this temp table to an update query so that my temp table was updated with number of orders that had a diff in currency between order intake and shipping.

For example,
the row -4 was updated with 30 orders ( 4 euro loss), the row 6 was updated with 22 orders ( 6 euro gain).

Then I deleted all rows from -1000 up to first row that had order.
Then I deleted all rows from 1000 down to last row that had order.

In my case the range were -5 to 8

Profit Orders
-5 1
-4 30
-3 0
-2 7
-1 2
0 3000
1 0
2 0
3 15
4 0
5 2
6 22
7 0
8 4

This result set is sent to client and viewed in a graph.

141 euro loss and 219 euro gain. Total diff is 78 euro gain. This is easily calculated from orders directly, but the sales manager wanted to break down the numbers per euro.

Without my number function, I would get

-5 1
-4 30
-2 7
-1 2
0 3000
3 15
5 2
6 22
8 4

Almost the same but slightly misleading.

You can also (and with less complexity) do it
(i just altered ur code)

CREATE FUNCTION dbo.fnSeqNumbers
(@LowLimit INT = 1,@HighLimit INT)
RETURNS @Values TABLE(Value INT)
AS
BEGIN

DECLARE @cnt int
IF @LowLimit > @HighLimit
SELECT @cnt = @LowLimit,
@LowLimit = @HighLimit,
@HighLimit = @cnt

set @cnt = @lowlimit
while @cnt <= @highlimit
    begin
    INSERT @Values VALUES (@cnt)
    set @cnt=@cnt+1
    end
RETURN
END

No, no, no... Bad idea.

Whatif you need 1 000 000 values? Then your code has to do 1 000 000 inserts in to table variable. Run and check the time it takes to do that. On my machine it took about 10 minutes.

My original function tool 8 seconds.

I want SPEED!