Click here to Skip to main content
11,717,509 members (80,365 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL-server-2005
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]
DECLARE @RoleID INT,@StartingPos INT,@RecPos INT,@RightId INT
SET @StartingPos=1
WHILE @StartingPos<=LEN(@roles)
    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

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,
DECLARE @RoleId INT,@StartingPos INT,@RecPos INT,@RightId INT
UPDATE Roles SET RoleName=@RoleName WHERE Id=@Id
SET @StartingPos=1
WHILE @StartingPos<=LEN(@roles)
	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
Posted 26-Mar-13 21:35pm
Prasad Khandekar at 27-Mar-13 3:55am
What's the value of @RoleId in UPDATE procedure?
sahmed4 at 27-Mar-13 4:18am
HI prasad,
Have a look in my table structure

select * from Roles
Id RoleName RoleCode
12 Admin NULL
13 Manager NULL
14 Manager2 NULL

Select * from rights
Id RightId RoleId
22 1 12
23 2 12
24 1 13
25 2 13
26 3 13
27 1 14
28 2 14
29 5 14
30 8 14

If i select Manager2 to Edit then My @RoleId values will be 1 2 5 & 8 now i need to update these values based on passing all these id's as 1 2 5 & 8 individually
Prasad Khandekar at 27-Mar-13 5:37am
I am assuming that it's the right id that gets changed for a particular Role. If I am correct then rather than updating individually which will require you to either know the old right id or the id in Rights table. I will say use DELETE followed by an INSERT. SO you update procedure will Update The RoleName in Roles table (The SQL for which is correct in your code). Then you will fire a DELETE Statement DELETE FROM rights WHERE RoleId = @Id. Then insert the rights as usual using code similar to INSERT procedure. Basically I will suggest you to right Three Procedure. One For Insert into ROles. One for INsert into Rights and One for Update RoleName. You can then call them in desired sequence.
Currently you will not be able to fire an update for Rights. The reason being for a RoleId there can be multiple records in Rights table and the PK (ID) of the rights table is unknown. You are not passing. Only unique column in Rights table is ID. The other is a combination RoleID + RightId. But you can not use later as using it requires following sql. UPDATE rights SET RightId = @RecSet WHERE RoleId = @Id AND RIghtId = @OldRightId.

sahmed4 at 27-Mar-13 8:36am
Yes prasad,
I have did it like tat only for time being ..deleted the current roleids and inserted it again..
Maciej Los at 28-Mar-13 3:07am
If you select Manager2, then Roles.Id = 14 and rights.RightId = 1,2,5,8 (not RoleId!!!).
What do you want to handle? I don't get it...
When you execute this line: UPDATE Roles SET RoleName=@RoleName WHERE Id=@Id nothing is changing in rights table. What's for looping through the RightId for updated name of role?
ryanb31 at 27-Mar-13 7:08am
In the insert you are inserting RecSet as the rightid but then you are using Rid as the record to update. Based on your explanation it sounds like the code should work just about the same.

1 solution

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

Solution 1

Please, read my comment...
I don't know what you really want to do, but the answer for the question: "How to split RightId from string like that: '1,2,5,8'?" is here: How to Loop through value sof Table under SP[^]

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 689
1 OriginalGriff 255
2 F-ES Sitecore 210
3 Jochen Arndt 120
4 Maciej Los 120
0 Sergey Alexandrovich Kryukov 1,144
1 Maciej Los 509
2 OriginalGriff 505
3 Richard MacCutchan 440
4 CHill60 415

Advertise | Privacy | Mobile
Web03 | 2.8.150901.1 | Last Updated 28 Mar 2013
Copyright © CodeProject, 1999-2015
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