carl brooks carl brooks - 4 months ago 5
SQL Question

How to create an increment table on execution

I want to know how to create an increment table, so that when a procedure is executed, the table contains the same number of rows as the parameter used to execute the procedure.

For example if I perform this:

EXEC PROCEDURE [dbo].[insert_number] 5


I should have a table (let's call the table 'Number') that displays the following:

NumberID
1
2
3
4
5


If I change the number in the EXEC to below:

EXEC PROCEDURE [dbo].[insert_number] 7


It should output:

NumberID
1
2
3
4
5
6
7


I don't want to use the sys.objects method as that requires using the master db.

What is the best way to perform this type of insert?

Thank you

Answer

Misunderstood the question,so the question is the table should be autoincremented based on parameter..

create proc usp_test1 
(
@n int
)
as
begin
if object_id('number','u') is not null
drop table number

create table dbo.number
(
id int
);


with cte
as
(
select 1  as n
union all
select n+1 from cte
where n<@n
)
insert into number
select * from cte c  where not exists(select 1 from number n where n.id=c.n)

end

You could also create table only once and comment out table creation script

Comments