Error in Updating the Sp with splitted values

Question

4.00/5 (1 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
========================

SQL

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
=============

SQL

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 21:35pm

sahmed4

Add a Solution

Comments

Prasad Khandekar 27-Mar-13 3:55am

What's the value of @RoleId in UPDATE procedure?

sahmed4 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 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 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 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?

ZurdoDev 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-03-27T21:12:00

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[^]

Posted 27-Mar-13 21:12pm

Maciej Los