Replacement for Cursor
-
Hi ,
I need to display the details of employees and their access to different modules . The problem I have is, a employee can have access to multiple modules. Employee Details are saved in Emp table and Module Details are saved in Module table and access rights are saved EmpModMap table.
I need to display the details as given below
EMP Module
1 A,B,C
2 B,D
3 D,F,G
I am retrieving Module details using a cursor and updating the values by concatenating with ','. Is there any alternate for this problem without using a cursor.
Date is saved like this
EmployeeID
1
2
3
ModuleID ModuleName
1 A
2 B
3
-
Ravi
What I would do is use a SELECT statement with ORDER BY EmployeeID at the end and let my front end do the presentation of the data. If you have to use T-SQL to do this for you, search this site for "comma separated list" or "concatenation". The question has been answered a lot of times.
John
-
Yup and here's the script I always post
: IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROM dbo.SysColumns C
WHERE C.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BY C.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply