如何在SP下循环使用值sof表 [英] How to Loop through value sof Table under SP

问题描述

您好我需要将一个表的值循环到另一个表，同时更新请求找到任何soln ..

Hi I have an requirement where i need to loop through the values of one table to another while updating pleas find any soln..

SET @Rid=(SELECT ID FROM RIGHTS WHERE RoleId=@RoleId)  

如果我得到Rid的值为

Rid

====

2

4

6

7



现在我需要在循环中传递所有这四个值来更新另一个表，其id值为2 4 6& 7.我希望我的要求是明确的

If i am getting a values of Rid as
Rid
====
2
4
6
7

Now i need to pass all theses four values in a loop to update the another table with an id values as 2 4 6 & 7. I hope my requirement is clear

推荐答案

sahmed4写道：

sahmed4 wrote:

如果我有@Roles值=1,3,5,7,8，将在权限表中的所有新创建的行中单独插入1 3 5 7 8但是在更新时如何处理此

if i have @Roles values="1,3,5,7,8," that will insert in as 1 3 5 7 8 individually in all the newly created rows in rights table but while updating how do handle this

看一下这个例子：

Have a look at this example:

DECLARE @sRoles NVARCHAR(100)
DECLARE @roles TABLE([Roles] NVARCHAR(100))

SET @sRoles = '1,3,5,7,8,'

INSERT INTO @roles ([Roles])
VALUES (@sRoles)

SELECT *
FROM @roles

;WITH SplitedString AS
(
		SELECT LEFT([Roles],CHARINDEX(',',[Roles])-1) AS [Role], RIGHT([Roles],LEN([Roles])-CHARINDEX(',',[Roles])) AS Remainder
        FROM @roles
        WHERE [Roles] IS NOT NULL AND CHARINDEX(',',[Roles])>0
    UNION ALL
    SELECT LEFT(Remainder,CHARINDEX(',',Remainder)-1), RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
        FROM SplitedString
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
    UNION ALL
    SELECT Remainder, NULL
        FROM SplitedString
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
)
SELECT *
FROM SplitedString
WHERE [Role]!=''
ORDER BY [Role]

结果：

Result:

Role    Remainder
1	3,5,7,8,
3	5,7,8,
5	7,8,
7	8,
8	

更新你的桌子，你应该使用这样的东西：

To update your table, you shold use something like this:

INSERT INTO TableName (Column1)
SELECT *
FROM SplitedString
WHERE [Role]!=''
ORDER BY [Role]

这篇关于如何在SP下循环使用值sof表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！