使用拆分值更新Sp时出错 [英] Error in Updating the Sp with splitted values
问题描述
您好。同时将值插入表权限我得到当前自动增量,如果我有@Roles值=1,3,5,7,8,将在所有新创建的行中单独插入1 3 5 7 8权利表
这是我的Insert Sp
========================
ALTER PROC [C244570_hrms12]。[h_AddRole]
(
@ RoleName NVARCHAR (MAX) ,
@ Roles NVARCHAR (MAX)
)
AS
DECLARE @ RoleID INT , @ StartingPos INT , @ RecPos INT , @ RightId INT
DECLARE @ RecSet VARCHAR ( 500 )
BEGIN
INSERT INTO ROLES(RoleName) VALUES ( @ RoleName )
SET @ StartingPos = 1
SET @RoleId =( SELECT IDENT_CURRENT(' ROLES'))
WHILE @ StartingPos< = LEN( @ roles )
BEGIN
SELECT @ RecPos = CHARINDEX(' ,', @ roles , @ StartingPos )
SELECT @ RecSet = SUBSTRING( @ roles , @ StartingPos ,@ Recpos- @ StartingPos)
SET @ RightId = @ RecSet
INSERT INTO 权利(RightId,RoleId) VALUES ( @ RightId , @ roleId )
SELECT @ StartingPos = @ RecPos + 1
END
END
在更新值时如何处理权限表的Id值我已经尝试了SP但没有工作请找到任何好的解决方案...
更新SP
=============
ALTER PROC [C244570_hrms12]。[h_UpdateRole]
(
< span class =code-sdkkeyword> @ Id INT ,
@ RoleName NVARCHAR (MAX),
@ Roles NVARCHAR (MAX)
)
AS
DECLARE @ RoleId INT , @ StartingPos < span class =code-keyword> INT , @ RecPos INT , @ RightId INT
DECLARE @ RecSet VARCHAR ( 5 00 ), @ Rid NVARCHAR ( 50 )
BEGIN
更新角色 SET RoleName = @ RoleName WHERE Id = @ Id
SET @ StartingPos = 1
SET @Rid =( SELECT ID FROM RIGHTS WHERE RoleId = @ RoleId)
WHILE @ StartingPos< = LEN( @ roles )
BEGIN
SELECT @ RecPos = CHARINDEX(' ,', @ roles , @ StartingPos )
SELECT @ RecSet = SUBSTRING( @ roles , @ StartingPos ,@ RecPos- @StartingPos)
SET @ RightId = @ RecSet
更新权利 SET RightId = @ RightId,RoleId = @ RoleId WHERE Id = @ Rid
END
END
< blockquote>请阅读我的评论...
我不知道你真正想做什么,但问题的答案是:如何拆分 RightId
来自字符串:''1,2,5,8''?在这里:如何在SP下循环索值表 [ ^ ]
Hi. while Inserting values into table Rights i am getting the Current Auto increment 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
This is my Insert Sp
========================
ALTER PROC [C244570_hrms12].[h_AddRole]
(
@RoleName NVARCHAR(MAX),
@Roles NVARCHAR(MAX)
)
AS
DECLARE @RoleID INT,@StartingPos INT,@RecPos INT,@RightId INT
DECLARE @RecSet VARCHAR(500)
BEGIN
INSERT INTO ROLES(RoleName) VALUES(@RoleName)
SET @StartingPos=1
SET @RoleId=(SELECT IDENT_CURRENT('ROLES'))
WHILE @StartingPos<=LEN(@roles)
BEGIN
SELECT @RecPos=CHARINDEX(',',@roles,@StartingPos)
SELECT @RecSet=SUBSTRING(@roles,@StartingPos,@Recpos-@StartingPos)
SET @RightId=@RecSet
INSERT INTO Rights(RightId,RoleId)VALUES(@RightId,@roleId)
SELECT @StartingPos=@RecPos+1
END
END
While Updating the values how to handle the Id values of rights table i have tried the SP but not working please find any good soln...
Update SP
=============
ALTER PROC [C244570_hrms12].[h_UpdateRole]
(
@Id INT,
@RoleName NVARCHAR(MAX),
@Roles NVARCHAR(MAX)
)
AS
DECLARE @RoleId INT,@StartingPos INT,@RecPos INT,@RightId INT
DECLARE @RecSet VARCHAR(500),@Rid NVARCHAR(50)
BEGIN
UPDATE Roles SET RoleName=@RoleName WHERE Id=@Id
SET @StartingPos=1
SET @Rid=(SELECT ID FROM RIGHTS WHERE RoleId=@RoleId)
WHILE @StartingPos<=LEN(@roles)
BEGIN
SELECT @RecPos=CHARINDEX(',',@roles,@StartingPos)
SELECT @RecSet=SUBSTRING(@roles,@StartingPos,@RecPos-@StartingPos)
SET @RightId=@RecSet
UPDATE Rights SET RightId=@RightId,RoleId=@RoleId WHERE Id=@Rid
END
END
Please, read my comment...
I don''t know what you really want to do, but the answer for the question: "How to splitRightId
from string like that: ''1,2,5,8''?" is here: How to Loop through value sof Table under SP[^]
这篇关于使用拆分值更新Sp时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!