Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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)  
 
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
Posted 26-Mar-13 21:52pm
sahmed4562
Comments
gvprabu at 27-Mar-13 3:17am
   
Hi, Ur Question is not clear....
In 2nd table what based u need to Update @RID value...

Use UPDATE Statement Directly... like as follows
 
UPDATE M SET M.RID=R.RID
FROM Main_Table M
INNER JOIN RIGHTS R ON R.RoleId=M.RoleID
sahmed4 at 27-Mar-13 3:30am
   
Hi.
while Inserting values into table Rights i am getting the Current Auto increment value but how do i do this for update sp 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
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
 

this is my 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
ENDs
END
 

 

 
gvprabu at 27-Mar-13 3:41am
   
Give some sample Data ur table Structure... and
in Update SP how u will get @RoleId values, nowhere u assigned any value for this variable.
sahmed4 at 27-Mar-13 4:20am
   
Please find out this link
http://www.codeproject.com/Questions/568241/ErrorplusinplusUpdatingplustheplusSppluswithplussp
specified my problem in elaborately...

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

sahmed4 wrote:
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]
  Permalink  
Comments
sahmed4 at 28-Mar-13 1:32am
   
Maciej los.
Please refer my descriptive problem in the below link and try to find out any solution for updating the records based on role id values
 
Error in Updating the Sp with splitted values[^]
Maciej Los at 28-Mar-13 3:13am
   
Please, see my comment and answer for above post.

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

  Print Answers RSS
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 OriginalGriff 120
3 PIEBALDconsult 110
4 Zoltán Zörgő 99
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 27 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100