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. 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
Posted 26-Mar-13 22:35pm
sahmed4562
Comments
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.
 
Regards,
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
good
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[^]
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 290
1 Jochen Arndt 165
2 DamithSL 125
3 PIEBALDconsult 110
4 Garth J Lancaster 90
0 OriginalGriff 5,790
1 DamithSL 4,601
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,195


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 28 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