function to get multiple values

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 2008 Forums

Transact-SQL (2008)

function to get multiple values

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-05-27 : 02:09:28

i have created one function to convert roleid to role name:

create FUNCTION [dbo].[FN_ROLES_ID_TO_NAME] (@FN_ROLES_ID int)
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @FN_ROLE_NAME varchar(300)
SELECT @FN_ROLE_NAME = COALESCE(ROLE,'')+','
FROM PNET_MASTER_ROLES
WHERE ROLEID = @FN_ROLES_ID
RETURN @FN_ROLE_NAME
END

in this function, only single role value only converting to name

if i give roleid 1 means it is converting to corresponding role name, but

if the column contains multiple roles like 1,2 means, its not converting to name

i want to display m,ultiple roles , for example 1,2 means admin,hr like that

can v do that in above function?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 02:21:56

Try:
where ROLEID like '%'+convert(varchar(10),@FN_ROLES_ID)+'%'

No, you're never too old to Yak'n'Roll if you're too young to die.

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-05-27 : 02:35:36

no its not working, its giving error like:

Conversion failed when converting the varchar value '3,8,6' to data type int.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 02:38:47

Please show what you have done and example data.

No, you're never too old to Yak'n'Roll if you're too young to die.

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-05-27 : 02:48:30

The below will explain u the situation

I HAVE SOME RECORDS IN TABLE A:

EMPLOYEEID ROLESID
1 2
2 1,2
3 5
4 2,4,6,

THIS ROLESID IS COMING FROM MASTER TABLE
WHICH HAS

ROLEID ROLE
1 A
2 B
3 C
4 D
5 E
6 F

NOW I WANT THE RESULT LIKE BELOW:

EMPLOYEEID ROLE
1 B
2 A,B
3 E
4 B,D,F

for this i created one function to convert roleid to rolename , the function is:

CREATE FUNCTION [dbo].[FN_ROLES_ID_TO_NAME] (@FN_ROLES_ID int)

RETURNS VARCHAR(MAX) AS

BEGIN

DECLARE @FN_ROLE_NAME varchar(300)

SELECT @FN_ROLE_NAME = COALESCE(ROLE,'')+','

FROM PNET_MASTER_ROLES

WHERE ROLEID like '%'+convert(varchar(10),@FN_ROLES_ID)+'%'

RETURN @FN_ROLE_NAME

END

i called this function into my stored procedure:

CREATE PROCEDURE [dbo].[SP_ROLES_VIEW]

@PI_EMPLOYEEID integer

AS

SELECT

PI_EMPLOYEEID,

dbo.FN_ROLES_ID_TO_NAME(AR_ADDITIONALROLESID) AS ROLES,

AR_ENABLEACCESS

FROM PNET_PFILE_datatable1 A

WHERE PI_EMPLOYEEID =@PI_EMPLOYEEID;

now the problem is if roleid is single value like 1 or 3 means its converting to role name and displaying.

If it is multiple value like 1,2 or 3,6,8 means its not displaying, giving error like:

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value '3,8,6' to data type int.

PLS HELP ME TO GET THIS IN QUERY

THX IN ADVANCE

With Thanks & Regards,

G.Arun

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-27 : 03:11:31

try


CREATE FUNCTION [dbo].[FN_ROLES_ID_TO_NAME] (@FN_ROLES_ID varchar(300))

RETURNS varchar(MAX) AS

BEGIN

DECLARE @FN_ROLE_NAME 	varchar(300)

SELECT 	@FN_ROLE_NAME = COALESCE(ROLE, '') + ',' +  ROLE

FROM 	PNET_MASTER_ROLES

WHERE	',' + @FN_ROLES_ID + ','	LIKE '%,' + CONVERT(varchar(10), ROLEID) + ',%'

RETURN 	@FN_ROLE_NAME

END

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

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-05-27 : 04:15:37

thx for ur function, but still its not working correct,

it is displaying the same value for two times like

unithead,unithead
hr,hr

like that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources