如何在存储过程中插入1-9999值表 [英] how to insert 1-9999 values table in stored procedure
本文介绍了如何在存储过程中插入1-9999值表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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屋!
查看全文