SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Tally Generator

By Sergiy Floka,

The function is based on well known Tally function published on SSC.

I added parameters to make it more flexible and easier to use in complicated queries.

Use cases:

Sequential numbers from 1 to 100, step 1:

select N 

from dbo.TallyGenerator (1,100, null, 1)
100 sequential numbers starting from 15 , step 1:
select N 
from dbo.TallyGenerator (15,null, 100, 1)
Counting down from 100 to 1, step 11:
select N 
from dbo.TallyGenerator (100,1, null, -11)
Top 100 numbers less or equal 256:
select N 
from dbo.TallyGenerator (256, null, 100,-1)

Total article views: 996 | Views in the last 30 days: 9
 
Related Articles
FORUM

Select just three octets from IP number

Select just three octets from IP number

FORUM

help me to select ROW_NUMBER in sql server 2000

help me to select ROW_NUMBER in sql server 2000

FORUM

How to get consecutive record number in two select statement

How to get consecutive record number in two select statement

FORUM

Select Same row X number of times

select query to return the same row X number of times

SCRIPT
Tags
function    
numbers    
tally    
 
Contribute