June 4, 2012 at 4:06 am
--exec [dbo].[GenerateSeries] 100
alter Procedure [dbo].[GenerateSeries]
@int bigint=NULL
AS
BEGIN
WITH mycte AS
(
SELECT 1 id , 100 ToID
UNION ALL
SELECT id + 100 ,(id + 100)+100-1
FROM mycte
WHERE id + 1 < @int
)
SELECT id ,ToID
FROM mycte
OPTION (MAXRECURSION 0)
End
Using the Above procedure i get 1-100 , 101 - 200
what i want is if i pass to any number it should generate the series up to that number.
e.g.
exec [dbo].[GenerateSeries] 100
Result : 1-100
exec [dbo].[GenerateSeries] 105
Result : 1-100,101-105
exec [dbo].[GenerateSeries] 95
Result : 1-95
exec [dbo].[GenerateSeries] 225
Result : 1-100,101-200,201-225 And So on
Please help me in generating the above output.
thanks
vineet
June 4, 2012 at 5:20 am
ALTER Procedure [dbo].[GenerateSeries]
@int int=NULL
AS
BEGIN
;WITH mycte AS
(
SELECT 1 id
, CASE
WHEN @int > 100 THEN 100
ELSE @int
END AS ToID
UNION ALL
SELECT
ID + 100
,(id + CASE
WHEN @int > ToID + 100 THEN 100
ELSE @int - Toid
END)
+CASE
WHEN @int > 100 THEN 100
ELSE @int
END -1
FROM mycte
WHERE Toid + 1 <= @int
)
SELECT id ,ToID
FROM mycte
OPTION (MAXRECURSION 0)
END
edited,
I've changed the @int from a bigint to an int, this stopped having to deal with the implicit convertion in the CTE
Hope this helps
June 4, 2012 at 8:41 am
June 4, 2012 at 10:19 am
I used to use the sys.objects table and use Row_Number() to generate arbitrary sequences, but that is dependent on the number of rows in the sys.objects table.
Now I do something like this:
CREATE PROCEDURE Numbers @Upper_Limit Int
AS
WITH Base (Digit) AS
(
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
)
-- Create a table query with numbers 1-100,000 as rows
-- by joining the Base table once for each decimal place...
SELECT
Number
FROM
(
SELECT
D5.Digit * 10000 +
D4.Digit * 1000 +
D3.Digit * 100 +
D2.Digit * 10 +
D1.Digit + 1 AS Number
FROM
Base D1 CROSS JOIN
Base D2 CROSS JOIN
Base D3 CROSS JOIN
Base D4 CROSS JOIN
Base D5
) All_Numbers
WHERE
Number <= @Upper_Limit
ORDER BY
Number
June 4, 2012 at 11:05 am
We have a indexed table in our database called Tally but on the fly I usually just cross join the sys.all_columns tables multiple times. My solution is below.
DECLARE @INT BIGINT = 300025
;WITH CTE
AS (
SELECT TOP 20000 n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM sys.all_columns c1 CROSS JOIN sys.all_columns c2)
SELECT bseries = n*100-99, eseries = CASE WHEN n*100 >= @INT THEN @INT ELSE n*100 END
FROM CTE
WHERE n*100-99 <= @INT OR n*100 <= @INT
;WITH CTE
AS (
SELECT TOP 20000 n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM sys.all_columns c1 CROSS JOIN sys.all_columns c2)
SELECT STUFF((
SELECT ',' + CAST(n*100-99 AS VARCHAR(MAX)) + '-' +
CAST(CASE WHEN n*100 >= @INT THEN @INT ELSE n*100 END AS VARCHAR(MAX))
FROM CTE
WHERE n*100-99 <= @INT OR n*100 <= @INT
FOR XML PATH ('')),1,1,'')
June 4, 2012 at 7:36 pm
If you're trying to mimic the results of your SP, i.e., the range appears in two columns, I think this is a pretty simple approach.
DECLARE @INT INT = 225
;WITH Tally
AS (
SELECT TOP ((@INT+100)/100) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM sys.all_columns a1 CROSS JOIN sys.all_columns a2)
SELECT SeriesStart=1+100*(n-1), SeriesEnd=CASE WHEN @INT<100*n THEN @INT ELSE 100*n END
FROM Tally
Results:
SeriesStart SeriesEnd
1 100
101 200
201 225
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 5, 2012 at 2:14 am
oops, I charged off in the wrong direction and was rightly brought back in line.
I have rewritten the the proc using a Tally Table
ALTER PROCEDURE [dbo].[GenerateSeries]
@int BIGINT = NULL
AS
BEGIN
; with T1 (n)as (
select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all select 1 union all select 1 )
, T2 (n) as (select a.n from T1 a cross join T1 b)
, T3 (n) as (select a.n from T2 a cross join T2 b)
, T4 (n) as (select a.n from T3 a cross join T3 b)
,Tally (n) as (select top (@int) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) from T4)
SELECT
ID=1+(100*(n-1))
, ToID=CASE WHEN @INT < (1+(100*n)) THEN @INT ELSE 100*n END
from Tally
where 1+(100*(n-1)) <= @int
END
@ Dwain.c, I tried not to copy your example, but having read it I could not help but be heavily influenced:-)
I did notice that your example does not handle the range 1 - 100 that was required by the OP.
June 5, 2012 at 3:05 am
440692 I am just a number (6/5/2012)
I did notice that your example does not handle the range 1 - 100 that was required by the OP.
Ooops! You are correct. But if my poorly tested attempt led to a good conclusion I'm happy.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply