How to create a stored proc which takes a value and a comma separated list

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

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 7-Jun-14 2:26am

ganesh.dks

Add a Solution

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.

2 solutions

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)

OriginalGriff · Accepted Answer · 2014-06-07T02:39:00

Solution 2

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]

Posted 7-Jun-14 2:39am

OriginalGriff

Updated 7-Jun-14 23:07pm

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.

**Nirav Prabtani** · Accepted Answer · 2014-06-07T02:37:00

Solution 1

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

Posted 7-Jun-14 2:37am

Nirav Prabtani

Updated 8-Jun-14 18:44pm

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