Click here to Skip to main content
15,938,218 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Hi,

I need help on creating a stored procedure which takes a parameter @roleID and a comma separated list @UserIDs.

I need to update or insert into a table which has 2 columns userID and RoleID.
From the comma separated list I need to split the string and get each userID then use it to in an update or Insert Statement where I assign roleID as @roleID to each user in the list. And this I should do for all the userIDs in the list.

please help me construct such an SP.

Thanks in Advance.
Posted
Comments
[no name] 7-Jun-14 8:29am    
Okay so what have you tried so far?
ganesh.dks 7-Jun-14 13:34pm    
I know I made a mistake using Tran twice , but code doesn't throw any error, but instead shows the number of affected rows. And when I check the table nothing seems to have happened , no update at all.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[UserAdmin_UserRoles_IU]
@RoleID int,
@UserIDs varchar(max)
As
Begin
BEGIN TRAN
Begin transaction
delete from UserRoles where RoleID = @RoleID
IF (@@ERROR <> 0) GOTO ERR_HANDLER
Insert into UserRoles (UserID,RoleID )
Select
tbl.Item ,@RoleID RoleID
--Also tried this -- tbl.Item UserID, @RoleID RoleID
from
dbo.fnSplit(@UserIDs,',') tbl

IF (@@ERROR <> 0) GOTO ERR_HANDLER
commit transaction
ERR_HANDLER:
BEGIN
ROLLBACK TRAN
End
End
ganesh.dks 7-Jun-14 14:25pm    
Also this is to delete and insert not to update so I would Like to know how to insert values with a scalar parameter and a list parameter.

Have a look at this: Using comma separated value parameter strings in SQL IN clauses[^] - it doesn't do exactly what you want, but it shows you how to handle the "donkey work" of the comma separated values. You should be able to handle it from there.


[EDIT]
OK, you haven't quite got the hang of this, have you? :laugh:
SQL
delete from UserRole where RoleID = @RoleID
--and UserID=(Select tbl.id from dbo.CSVToTable(@UserIDs) tbl)

Won't WOrk: it returns a set of result, not a single result.
Try this:
SQL
delete from UserRole where RoleID = @RoleID
AND UserID IN (Select id from dbo.CSVToTable(@UserIDs))


Then for your INSERT:
SQL
INSERT INTO UserRole(UserID, RoleId) SELECT id, @RoleID FROM dbo.CSVToTable(@UserIDs)


[/EDIT]
 
Share this answer
 
v2
Comments
ganesh.dks 7-Jun-14 15:04pm    
OK Thanks a lot I got it, It was long back in 2009 I used to teach sql server and c#.net to graduates. But it took years for me to get into the software industry when I forgot almost many important things.

I have got one problem though, when I use your function in my stored procedure it still throws the same problem u mentioned. I am having to manually pass the ID string. I am not able to pass the variable.

Here is my proc:-

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[UserAdmin_UserRoles_IU]
@RoleID int,
@UserIDs varchar(max)
As
Begin
Begin transaction
delete from UserRole where RoleID = @RoleID
--and UserID=(Select tbl.id from dbo.CSVToTable(@UserIDs) tbl)
IF (@@ERROR <> 1)
begin
Insert into UserRole (UserID,RoleID )
Select tbl.id ,@RoleID RoleID
from
dbo.CSVTOTable(@UserIDs) tbl
end
IF (@@ERROR <> 1)
begin
commit transaction
end
else
begin
ROLLBACK TRAN
End
End

However This doesn't work


DECLARE @LIST VARCHAR(200)
SET @LIST = '11,12,13,14,15,16';
[UserAdmin_UserRoles_IU] 2 , @List

The same problem you told earlier (conversion error).
Does that mean that I will have to go and write the same code again. Pardon if I am foolish. I need to learn a lot from experts like you.
ganesh.dks 7-Jun-14 15:28pm    
I tried doing this but still didn't get it to work:-


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[UserAdmin_UserRoles_IU]
@RoleID int,
@UserIDs varchar(max)
As
BEGIN
Begin transaction
--declare @Instr varchar(max)
declare @TempTab table (id int)
delete from UserRole where RoleID = @RoleID
--and UserID=(Select tbl.id from dbo.CSVToTable(@UserIDs) tbl)
IF (@@ERROR <> 1)
BEGIN

SET @UserIDs = REPLACE(@UserIDs + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @UserIDs ) <> 0
BEGIN
SELECT @SP = PATINDEX('%,%',@UserIDs)
SELECT @VALUE = LEFT(@UserIDs , @SP - 1)
SELECT @UserIDs = STUFF(@UserIDs, 1, @SP, '')
INSERT INTO @TempTab(id) VALUES (@VALUE)
Insert into UserRole (UserID,RoleID )
Select tbl.id ,@RoleID RoleID
from
@TempTab tbl
END
IF (@@ERROR <> 1)
BEGIN
commit transaction
END
ELSE
BEGIN
ROLLBACK TRAN
END
END
END
OriginalGriff 8-Jun-14 5:07am    
Answer updated
ganesh.dks 8-Jun-14 12:47pm    
Sorry but I am unable to comprehend.
According to the code you gave me it will delete all the users(specified in the list) with the given role. But if we are assigning a new role there isn't any need to delete because there are no such users assigned to the role.

And again if a user has a role already, and I intend to change his role , I am not fetching such an ID from the frontend because there is no support for unchecked event in the treeview I am using. The page would simple iterate and get the selected list only.

With these selected list I generate the ID list from the underlying DataSet. Now in your query the old role will still be there for a user even though the user intends to revoke his role by unchecking the user checkbox. Where as what I want is to delete all users with the given role everytime and insert or assign the role to the specified users list. Which is why I wanted to delete the whole set of users who had this role already. Kindly help me do this. And a lot many thanks for patiently answering my queries.
ganesh.dks 8-Jun-14 12:56pm    
I Tried using this,

delete from UserRole where RoleID = @RoleID AND UserID IN (Select UserID from UserRole)
INSERT INTO UserRole(UserID, RoleId) SELECT id, @RoleID FROM dbo.CSVToTable(@UserID)

This once did what I wanted but also threw this error later.

Msg 217, Level 16, State 1, Procedure AssignRoleToUser, Line 7
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Its a simple procedure and I am unable to get right. I don't know perhaps I have forgotten everything.
Use in clause for that
By this procedure you can assign same roleID to multiple comma separated UserID

SQL
CREATE PROCEDURE [sp_Name]
@RoleID INT=0,
@UserID varchar(max)='',
@strQuery nvarchar(max)=''
AS
	BEGIN
		select @strQuery='UPDATE TableName SET [RoleID]='+convert(varchar,@RoleID)+' where UserID IN ('+@UserID+')'
		exec (@strQuery)
	END
GO
 
Share this answer
 
v2
Comments
ganesh.dks 7-Jun-14 11:12am    
It wont work, throws the following error:-

Msg 245, Level 16, State 1, Procedure AssignRoleToUser, Line 6
Conversion failed when converting the varchar value '11,12,13,14,15' to data type int.

The UserID is a comma separated string.
Nirav Prabtani 9-Jun-14 0:45am    
try updated solution 1

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900