Retrieve Multiple Roleid's by passing csv strings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieve Multiple Roleid's by passing csv strings

Author

Topic

csri
Starting Member

45 Posts

Posted - 2008-06-24 : 10:02:35

Dear All
I have a table Roles with columns(RoleId,Rolename)
I have another table userrole with columns(userid,roleid)

I need to retrieve the roleids when rolenames(multiple strings in csv format) are passed.
The stored procedure is as follows
create procedure GetRoleIds(@Rolenames varchar(200),@RoleIds int output) as
begin
end
declare @roleidlist int
Exec GetRoleIds 'Admin,Manager,Developer',@roleidlist output
print @roleidlist

The output must be 1,2,3.

Roles table data

Roleid rolename
1 Admin
2 Manager
3 Developer

How to do this.Please help

Thanks
srinivas

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 10:16:51

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

csri
Starting Member

45 Posts

Posted - 2008-06-24 : 10:48:16

Hi Madhivanan
Thanks for your reply.I have already gone through the link.But my problem is
I have to pass to stored procedure a list of strings and retrieve roleid's from that list.
eg
create procedure GetRoleIds(@rolenamelist varchar(200),@roleidlist int output) as
select @roleidlist=roleid from roles where rolename in(@rolenamelist)
How to get this.Please help.

Thanks
srinivas

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 10:52:17

So you want to retreive values in csv format?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 10:55:05

quote:
select @roleidlist=roleid from roles where rolename in(@rolenamelist)

if the above query returns multiple roleid, you can't assign it to a variable. Doing so will only get assign the last value.

CREATE PROCEDURE GetRoleIds
        @rolenamelist varchar(200),
        @roleidlist int OUTPUT 
AS
BEGIN
        SELECT  r.roleid 
        FROM    roles r
                INNER JOIN dbo.csvtable(rolenamelist) c
                ON      r.rolename      = c.strval
END

csvtable can be obtain from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

KH
[spoiler]Time is always against us[/spoiler]

csri
Starting Member

45 Posts

Posted - 2008-06-24 : 10:56:47

Yes I want to retrive the values of roleids as 1 2 3 or 1,2,3 anything is ok for me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 10:58:31

[code]CREATE PROCEDURE GetRoleIds
@rolenamelist varchar(200),
@roleidlist varchar(200) OUTPUT
AS
BEGIN
SELECT @roleidlistr = coalesce(@roleidlist + ',', '') + CONVERT(varchar(10), r.roleid)
FROM roles r
INNER JOIN dbo.csvtable(rolenamelist) c
ON r.rolename = c.strval
END[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 11:00:38

quote:
Originally posted by csri

Yes I want to retrive the values of roleids as 1 2 3 or 1,2,3 anything is ok for me.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

csri
Starting Member

45 Posts

Posted - 2008-06-24 : 11:13:09

Hi khtan
My query returns multiple roleid.that is my problem

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 11:16:13

quote:
Originally posted by csri

Hi khtan
My query returns multiple roleid.that is my problem

Can you give more details on what you are trying?
Clearly post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

csri
Starting Member

45 Posts

Posted - 2008-06-24 : 11:17:25

Dear all
My Requirement is I have three tables.users(userid,username),roles(roleid,rolename)
userrole(userid,roleid).
My sp is Create procedure Getroleids(@username varchar(20),@rolenamelist varchar(200)) as
declare
@uid int,@roleid int
select @uid = username from user where username=@username
select @roleid = roleid from roles where rolename in(@rolenamelist)
//once I got my roleid which is having multiple values I need to insert
uid and roleid values into userrole table.
Please help me I am trying since Yesterday.

Thanks
Srinivas

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-24 : 11:26:44

You need this function, it saved me a bunch!!! Hope it helps you. then this is what I did..hope this is what you are looking for
INSERT INTO [table to insert into]
SELECT r.roleid
FROM roles r
Inner join int_CreateTableFromList(rolenamelist) csvdel
on r.roleName = csvdel.strJoinerField

SET NOCOUNT ON;
USE [your_database_name]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Creating str_CreateTableFromList Function

IF OBJECT_ID('dbo.str_CreateTableFromList') IS NOT NULL
DROP FUNCTION [dbo].[str_CreateTableFromList]
GO

CREATE FUNCTION [dbo].[str_CreateTableFromList]
(@list nvarchar(MAX),
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (strJoinerField varchar(4000) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL,
nstrJoinerField nvarchar(2000) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL) AS

/*
Object Name: str_CreateTableFromList

* History
By Date Description
-----------------------------------------
-- Author: Erland Sommarskog
-- http://www.sommarskog.se/arrays-in-sql-2005.html#iterative

*/
-----------------------------------------
BEGIN
DECLARE @endpos int,
@startpos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen

SET @startpos = 0
SET @endpos = charindex(@delimiter COLLATE SQL_Latin1_General_CP437_BIN, @tmpstr)

WHILE @endpos > 0
BEGIN
SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
@endpos - @startpos - 1)))
INSERT @tbl (strJoinerField, nstrJoinerField) VALUES(@tmpval, @tmpval)
SET @startpos = @endpos
SET @endpos = charindex(@delimiter COLLATE SQL_Latin1_General_CP437_BIN,
@tmpstr, @startpos + 1)
END

SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
END

INSERT @tbl(strJoinerField, nstrJoinerField)
VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
RETURN
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 11:49:30

quote:
Originally posted by csri

Dear all
My Requirement is I have three tables.users(userid,username),roles(roleid,rolename)
userrole(userid,roleid).
My sp is Create procedure Getroleids(@username varchar(20),@rolenamelist varchar(200)) as
declare
@uid int,@roleid int
select @uid = username from user where username=@username
select @roleid = roleid from roles where rolename in(@rolenamelist)
//once I got my roleid which is having multiple values I need to insert
uid and roleid values into userrole table.
Please help me I am trying since Yesterday.

Thanks
Srinivas

Why not just let the stored procedure inserting the record into the userrole table directly ?

CREATE PROCEDURE InsertUserRole
	@username 	varchar(20),
	@rolenamelist 	varchar(200) 
AS
BEGIN
	INSERT INTO userrole (userid, roleid)
	SELECT	u.userid, r.roleid
	FROM	users u
		CROSS JOIN dbo.csvtable(@rolenamelist) rl
		INNER JOIN roles r	ON	rl.stringval	= r.rolename
	WHERE	u.username	= @username
END

KH
[spoiler]Time is always against us[/spoiler]

csri
Starting Member

45 Posts

Posted - 2008-06-24 : 12:14:41

Dear All
Thank you very much for all your answers.Appreciated.I think the simple solution is
Create procedure Getroleids(@username varchar(20),@rolenamelist varchar(200)) as
insert into userrole(userid,roleid)select * from dbo.splitfunction(@rolenamelist)
In splitfunction I can create a function something like this
CREATE FUNCTION splitfunction(@rolenamelist varchar(200),@username varchar(20))
RETURNS @OutTable table(Userid int,roleid int)
AS
BEGIN
Select @userid = userid from users where username=@username
//finally insert into OutTable values(@userid,@roleidlist)
END
How to do this.Please help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 12:16:04

have you try the code i posted ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 12:17:07

[code]CREATE TABLE users
(
userid int identity (1,1),
username varchar(10)
)

CREATE TABLE roles
(
roleid int identity(1,1),
rolename varchar(10)
)

CREATE TABLE userrole
(
userid int,
roleid int
)

INSERT INTO users (username)
SELECT 'tom' UNION ALL
SELECT 'dick' UNION ALL
SELECT 'harry'

INSERT INTO roles (rolename)
SELECT 'admin' UNION ALL
SELECT 'operator' UNION ALL
SELECT 'user'

go

CREATE PROCEDURE InsertUserRole
@username varchar(20),
@rolenamelist varchar(200)
AS
BEGIN
INSERT INTO userrole (userid, roleid)
SELECT u.userid, r.roleid
FROM users u
CROSS JOIN dbo.CSVTable(@rolenamelist) rl
INNER JOIN roles r ON rl.stringval = r.rolename
WHERE u.username = @username
END

go

EXEC InsertUserRole @username = 'tom', @rolenamelist = 'admin, user'

SELECT *
FROM userrole

/*
userid roleid
----------- -----------
1 1
1 3
*/

DROP TABLE users
DROP TABLE roles
DROP TABLE userrole
DROP PROCEDURE InsertUserRole[/code]

KH
[spoiler]Time is always against us[/spoiler]

csri
Starting Member

45 Posts

Posted - 2008-06-24 : 12:30:36

Hi KH
Many Thanks for posting your response in no time.Can you please tell me how to write
a function like this.The link you have provided inserts the string names in the table.But I am passing rolenames and I want to insert roleid's and userid into the table inside the function.I don't know how to do that.Can you please help me.
CREATE FUNCTION splitfunction(@rolenamelist varchar(200),@username varchar(20))
RETURNS @OutTable table(Userid int,roleid int)
AS
BEGIN
Select @userid = userid from users where username=@username
//finally insert into OutTable values(@userid,@roleidlist)
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 13:10:57

quote:
Originally posted by csri

Hi KH
Many Thanks for posting your response in no time.Can you please tell me how to write
a function like this.The link you have provided inserts the string names in the table.But I am passing rolenames and I want to insert roleid's and userid into the table inside the function.I don't know how to do that.Can you please help me.
CREATE FUNCTION splitfunction(@rolenamelist varchar(200),@username varchar(20))
RETURNS @OutTable table(Userid int,roleid int)
AS
BEGIN
Select @userid = userid from users where username=@username
//finally insert into OutTable values(@userid,@roleidlist)
END

Are you trying to do this by joining some tables?

Madhivanan

Failing to plan is Planning to fail

csri
Starting Member

45 Posts

Posted - 2008-06-24 : 13:19:41

Hi Madhivanan
I want to do like this.Retrive the roleid's from rolenames while splitting the csv string and insert the roleid along with userid into the userrole table.Can you help me.
CREATE FUNCTION splitfunction(@rolenamelist varchar(200),@username varchar(20))
RETURNS @OutTable table(Userid int,roleid int)
AS
BEGIN
Select @userid = userid from users where username=@username
//finally insert into OutTable values(@userid,@roleidlist)
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 13:36:17

quote:
Originally posted by csri

Hi Madhivanan
I want to do like this.Retrive the roleid's from rolenames while splitting the csv string and insert the roleid along with userid into the userrole table.Can you help me.
CREATE FUNCTION splitfunction(@rolenamelist varchar(200),@username varchar(20))
RETURNS @OutTable table(Userid int,roleid int)
AS
BEGIN
Select @userid = userid from users where username=@username
//finally insert into OutTable values(@userid,@roleidlist)
END

Try this


Create function dbo.splitfunction(@rolenamelist varchar(200),@username varchar(20))
RETURNS @OutTable table(Userid int,roleid int)
AS
BEGIN
declare @userid int
Select @userid = userid from dev1.users where username=@username
while charindex(',',@rolenamelist)>0
begin
	insert into @OutTable values(@userid,substring(@rolenamelist,1,charindex(',',@rolenamelist)-1))
	select @rolenamelist=substring(@rolenamelist,charindex(',',@rolenamelist)-1,len(@rolenamelist))	
end
insert into @OutTable values(@userid,@rolenamelist)
Return
End

Madhivanan

Failing to plan is Planning to fail

csri
Starting Member

45 Posts

Posted - 2008-06-24 : 14:21:17

Hi Madhivanan
when executing the above code send by you .It is throwing error when executing the sp.
alter function dbo.splitfunction(@rolenamelist varchar(200),@username varchar(20))
RETURNS @OutTable table(Userid int,roleid int)
AS
BEGIN
declare @userid int
Select @userid = userid from users where username=@username
while charindex(',',@rolenamelist)>0
begin
insert into @OutTable values(@userid,substring(@rolenamelist,1,charindex(',',@rolenamelist)-1))
select @rolenamelist=substring(@rolenamelist,charindex(',',@rolenamelist)-1,len(@rolenamelist))
end
insert into @OutTable values(@userid,@rolenamelist)
Return
End
ALTER procedure Testrole123(@rolenames varchar(200),@username varchar(20)) as
insert into userrole select * from dbo.splitfunction(@rolenames,@username)

exec Testrole123 'Admin,Manager,Developer','Hari'

Error :Conversion failed when converting the varchar value 'Admin' to data type int.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources