how to insert 1-9999 values table in stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

CREATE PROCEDURE writeNo
AS
DECLARE @slno INT
SET @slno = 1
WHILE @slno <= 9999
BEGIN
SET NOCOUNT ON 
 INSERT INTO [dbslno].[dbo].tblslno(slno)
     values(@slno)  
SET @slno = @slno + 1
END
SELECT * FROM tblslno
GO

tried this and its not working.

Posted 22-Jul-15 20:22pm

kiran gowda8

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2015-07-22T20:56:00

Solution 1

You don't need to have a loop for getting a serial number 1..9999. This can be done without any loop/iteration at all. See below for a sampel code.

SQL

CREATE PROCEDURE writeNo
AS
BEGIN
	SET NOCOUNT ON 
	INSERT INTO [dbslno].[dbo].tblslno(slno)
	SELECT n
	FROM
	(
		SELECT TOP (9999) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
		FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
	) a

	SELECT *
	FROM	[dbslno].[dbo].tblslno
END
GO

exec writeNo
GO

Posted 22-Jul-15 20:56pm

_Asif_

Updated 22-Jul-15 22:35pm

v4

Comments

kiran gowda8 23-Jul-15 4:05am

how to insert that in to table??

_Asif_ 23-Jul-15 4:33am

Solution modified! Please check

Herman<T>.Instance 23-Jul-15 4:56am

Nice! +5

kiran gowda8 23-Jul-15 4:58am

thank you _Asif_.!!

Animesh Datta 23-Jul-15 5:14am

My 5!

Tshepo Kelebetse 23-Jul-15 5:15am

got it

Andy Lanng · Accepted Answer · 2015-07-22T22:51:00

Solution 2

I'd prefer something a little more self reliant:

SQL

CREATE PROCEDURE writeNo
AS
BEGIN

	WITH mycte AS (
		SELECT 1 AS number
		UNION ALL SELECT number + 1 FROM mycte
		WHERE number < 9999
	) 
	INSERT INTO [dbslno].[dbo].tblslno(slno)
	SELECT number FROM mycte
	OPTION (MAXRECURSION 9999)

END

Yay for recursive Common Table Expressions (cte) ^_^

Posted 22-Jul-15 22:51pm

Andy Lanng

Comments

_Asif_ 23-Jul-15 5:05am

+5 :)

Animesh Datta 23-Jul-15 5:14am

5!

Andy Lanng 23-Jul-15 5:24am

ooh so much attention. I blush ^_^

I might just point out that in this case Asif's solution is s little more efficient :)

kiran gowda8 23-Jul-15 6:31am

thank you Andy Lanng :)