SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bernabe Diaz / Get a range of numbers / Latest Posts

RE: Get a range of numbers

Tee Time — Fri, 25 May 2012 06:33:43 GMT

This may be only slightly related, but thought I'd share.I used this solution for creating sequential carton labels for shipping.I created a procedure that populates a table of sequential numbers by feeding in parameters from a SSRS report. In this case I feed the beginning and ending number.CREATE PROCEDURE SEQUENTIAL(@BEG_NUM INT,@END_NUM INT)ASDECLARE @start INTDECLARE @end INTSELECT @start = @BEG_NUM, @end = @END_NUMWHILE @start <= @endbeginINSERT INTO BUR20VM.dbo.SEQUENTIAL_TABLE(CTN_NUM_SEQ)VALUES(@start)SET @start = @start + 1ENDGOI then use the resulting table results in a cross join with another table, to get the label output.I should also note, when I go to run the next sequence of numbers (for the next shipment), the SSRS report first EXECutes a second stored procedure to delete all values from the previosly created SEQUENTIAL_TABLE, and then re-populates with the new sequence of numbers.I think this is a good illustration of a real world application for the sequential table. :-)

RE: Get a range of numbers

Gullimeel — Tue, 15 May 2012 09:14:16 GMT

Using a table with numbers is best way

RE: Get a range of numbers

Cadavre — Wed, 02 May 2012 08:31:13 GMT

[quote][b]michael vessey (5/2/2012)[/b][hr][quote][b]Cadavre (5/2/2012)[/b][hr][quote][b]michael vessey (5/2/2012)[/b][hr]depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) version[/quote]Actually, you should pretty much never use recursive CTEs for counting. [url=http://www.sqlservercentral.com/articles/T-SQL/74118/]See this article for why[/url].[/quote]run the CTE and the cross join version you posted and look at the execution plans.... then see the difference the CTE has it's place when dealing with small numbers[/quote]Did you have a gander at Jeff Moden's article that I linked? --> [url=http://www.sqlservercentral.com/articles/T-SQL/74118/]http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]

RE: Get a range of numbers

MVDBA — Wed, 02 May 2012 08:25:01 GMT

[quote][b]Cadavre (5/2/2012)[/b][hr][quote][b]michael vessey (5/2/2012)[/b][hr]depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) version[/quote]Actually, you should pretty much never use recursive CTEs for counting. [url=http://www.sqlservercentral.com/articles/T-SQL/74118/]See this article for why[/url].[/quote]run the CTE and the cross join version you posted and look at the execution plans.... then see the difference the CTE has it's place when dealing with small numbers

RE: Get a range of numbers

Cadavre — Wed, 02 May 2012 08:19:48 GMT

[quote][b]michael vessey (5/2/2012)[/b][hr]depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) version[/quote]Actually, you should pretty much never use recursive CTEs for counting. [url=http://www.sqlservercentral.com/articles/T-SQL/74118/]See this article for why[/url].

RE: Get a range of numbers

MVDBA — Wed, 02 May 2012 07:13:17 GMT

depends if you need a BIGINT or not - i'd suggest just using yours for anything when max-min > 32,767 - otherwise use the nice small (low resource) version

RE: Get a range of numbers

Cadavre — Wed, 02 May 2012 04:07:11 GMT

[quote][b]michael vessey (5/2/2012)[/b][hr]wouldn't this be easier to do with a recursive CTE?declare @start bigint=99;declare @end bigint=147;with x (num) as(select 1 as numUNION ALL SELECT num+1 from xwhere num <@end)select * from x where num between @start and @endoption (maxrecursion 10000);[/quote]Or . . .[code="sql"]DECLARE @start BIGINT = 97;DECLARE @end BIGINT = 250000;WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1),CTE2(n) AS (SELECT 1 FROM CTE x, CTE y),CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y),CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y),CTE5(n) AS (SELECT 1 FROM CTE4 x, CTE4 y),CTE6(n) AS (SELECT 0 UNION ALL SELECT TOP (@end-@start) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM CTE5 x, CTE5 y)SELECT @start+nFROM CTE6WHERE @start+n <= @end;[/code]Or. . .[code="sql"]DECLARE @start BIGINT = 97;DECLARE @end BIGINT = 1000000;SELECT @start+nFROM (SELECT 0 UNION ALL SELECT TOP (@end-@start) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master.sys.columns a, master.sys.columns b, master.sys.columns c) a(n)WHERE @start+n <= @end;[/code]

RE: Get a range of numbers

MVDBA — Wed, 02 May 2012 03:41:32 GMT

wouldn't this be easier to do with a recursive CTE?declare @start bigint=99;declare @end bigint=147;with x (num) as(select @start as numUNION ALL SELECT num+1 from xwhere num <@end)select * from x where num between @start and @endoption (maxrecursion 10000);

Get a range of numbers

diaz.bernabe — Tue, 01 May 2012 20:14:39 GMT

Comments posted to this topic are about the item [B]Get a range of numbers[/B]