-1

Here is my problem: I have a stored procedure in SQL Server 2012 which should do the following thing.

I will pass an input parameter @Range, and the stored procedure should insert values into a table starting from 0 to @Range-1.

CREATE PROC MyExample
     (@Range INT)
AS
BEGIN
   // Suppose the value of @Range is 100
   // So I should do INSERT into MyTable Values(0,1,2,3,4,5,6,......99)
END

Any idea how to achieve this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AR_Dev
  • 75
  • 2
  • 8
  • its seems as a homework and it is really easy , just add insert statement and isnide of it an argument. adding a loop – Moudiz Dec 29 '16 at 06:46
  • So you want to insert at row level or column level.By watching your query it seems you want to insert in that number of columns depending on the range?please clarify – vinay koul Dec 29 '16 at 06:50

5 Answers5

2

You can use while loop as below:

Declare @Index AS INT=0

WHILE @Index<@Range
BEGIN
    INSERT into MyTable Values(@Index)
    SET @Index=@Index+1
END
Sandip - Frontend Developer
  • 14,397
  • 4
  • 35
  • 61
  • Initially thought of going with this...But looking at insert statement of procedure i think insertion need to happen at the column level. – vinay koul Dec 29 '16 at 06:52
0

I am thinking your teacher may suspect why you use cte when you just learn a loop

CREATE PROC MyExample
(
    @Range INT,
)
AS
BEGIN
    ;WITH numbers AS 
    (  
        SELECT 0 AS Value WHERE @Range >= 0 -- Validate the @Range value too, try 0 or negative values
        UNION ALL SELECT Value + 1 FROM numbers WHERE Value + 1 < @Range
    )
    INSERT INTO MyTable
    SELECT * FROM numbers
    OPTION (MAXRECURSION 0)
END
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Eric
  • 5,675
  • 16
  • 24
  • thanks a ton! worked perfectly fine. Will do the valudations for the range as well . Thanks :) – AR_Dev Dec 29 '16 at 07:17
0
CREATE PROC MyExample
(
    @Range INT,
)
AS
BEGIN
declare @RANGE_COUNT int

select @RANGE_COUNT =@Range 
   //Suppose the value of @Range is 100
while @RANGE_COUNT<>0
begin
  //So I should do INSERT into MyTable Values(0,1,2,3,4,5,6,......99)
  INSERT into MyTable Values(@Range)
set @RANGE_COUNT = RANGE_COUNT -1
end
END
Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • This suggestion has some flaws... 1. where do you set the initial value of `@RANGE_COUNT`? 2. why is it numeric instead of int? 3, your loop condition is wrong. 4. Your insert is wrong - it will insert 100 each time. – Zohar Peled Dec 29 '16 at 06:58
  • @ZoharPeled check now please , as the point 4 its not clear from the OP – Moudiz Dec 29 '16 at 07:06
  • 1
    I think it's clear enough - he wants to insert the numbers between 0 and the value of `@Range`. withdrawn my downvote. – Zohar Peled Dec 29 '16 at 07:08
0

And here is a set based approach:

CREATE PROC MyExample
(
    @Range INT,
)
AS
BEGIN

    INSERT INTO MyTable (Number)
    SELECT TOP (@Range) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
    FROM sys.objects s1       
    CROSS JOIN sys.objects s2 
END

(Based on this SO post)

Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

Using tally table technique:

DECLARE @range INT = 100

SELECT TOP(@range) -1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn 
FROM 
    (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t1(n) CROSS JOIN --10
    (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t2(n) CROSS JOIN --100
    (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t3(n)            --1000
    --...continue to cover all possible @range values
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75