如何在存储过程中插入1-9999值表 [英] how to insert 1-9999 values table in stored procedure

问题描述

 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 
 
试过这个并且它无效。

解决方案

您不需要循环来获取序列号1..9999。这可以在没有任何循环/迭代的情况下完成。请参阅下面的示例代码。

 创建 程序 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  

我更喜欢更自信的东西：

  CREATE   PROCEDURE  writeNo 
  AS  
  BEGIN  
 
  WITH  mycte  AS （
  SELECT   1   AS 号码
  UNION   ALL   SELECT 号码+  1   FROM  mycte 
  WHERE 数字<  9999  
）
  INSERT   INTO  [dbslno]。[dbo] .tblslno（slno）
  SELECT  number  FROM  mycte 
  OPTION （MAXRECURSION  9999 ）
 
  END  

表示递归公用表表达式（cte）^ _ ^

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.

解决方案

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.

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

I'd prefer something a little more self reliant:

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) ^_^

这篇关于如何在存储过程中插入1-9999值表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！