FUNCTION [dbo].[fn_GetAvailableRepsContainingSkill]
( @intCategoryID int )
RETURNS @tblReps TABLE
	(
		ID int,
		FNAME  nvarchar(50),
		LNAME nvarchar(50),
		IM nvarchar(50),
		GROUP_NAME nvarchar(255),
		MGR1_NAME nvarchar(101),
		ID_MGR1 int,
		MGR2_NAME nvarchar(101),
		ID_MGR2 int,
		SKILL_LEVEL int,
		SKILL_LEVEL_NAME nvarchar(50),
		ID_GROUP int
	 )

 AS 
/* Return a table containing the rep first name, last name, ID, skill level, and group */
BEGIN
	INSERT @tblReps
		SELECT DISTINCT rep.ID, rep.FNAME, rep.LNAME, rep.IM, groups.GROUP_NAME,
			LTRIM(RTRIM( mgr1.FNAME + ' ' + mgr1.LNAME )) AS MGR1_NAME,
			rep.ID_MGR1,
			LTRIM(RTRIM( mgr2.FNAME + ' ' + mgr2.LNAME )) AS MGR2_NAME,
			rep.ID_MGR2, (SELECT TOP (1) skill_level FROM fn_GetRepSkill(rep.[ID], @intCategoryID )) AS 'SKILL_LEVEL',
			CASE SKILL_LEVEL
				WHEN 1 THEN
					cfg.SB_ROUTE_SKILL_LBL1
				WHEN 2 THEN
					cfg.SB_ROUTE_SKILL_LBL2
				WHEN 3 THEN
					cfg.SB_ROUTE_SKILL_LBL3
				WHEN 4 THEN
					cfg.SB_ROUTE_SKILL_LBL4
			END AS 'SKILL_LEVEL_NAME',
			groups.ID AS 'ID_GROUP'
		FROM CONFIG_INCIDENT AS cfg, REPS AS rep
		INNER JOIN REP_SKILLS repSkills
			ON repSkills.ID_REP = rep.ID
		LEFT OUTER JOIN GROUPS groups
			ON groups.ID = rep.ID_DEFAULT_GROUP
		LEFT OUTER JOIN REPS mgr1
			ON mgr1.ID = rep.ID_MGR1
		LEFT OUTER JOIN REPS mgr2
			ON mgr2.ID = rep.ID_MGR2
		WHERE repSkills.ID_CATEGORY IN (SELECT ID FROM fn_GetCategoryAncestry(@intCategoryID ))
		  AND rep.IS_AVAILABLE=1
		  AND rep.PENDING_DELETION != 1
		ORDER BY repSkills.SKILL_LEVEL, rep.FNAME, rep.LNAME, groups.GROUP_NAME
	RETURN
END

ALTER FUNCTION dbo.fn_GetAvailableRepsContainingSkill
(
	@intCategoryID INT
)
RETURNS @tblReps TABLE
	(
		ID INT,
		FNAME NVARCHAR(50),
		LNAME NVARCHAR(50),
		IM NVARCHAR(50),
		GROUP_NAME NVARCHAR(255),
		MGR1_NAME NVARCHAR(101),
		ID_MGR1 INT,
		MGR2_NAME NVARCHAR(101),
		ID_MGR2 INT,
		SKILL_LEVEL INT,
		SKILL_LEVEL_NAME NVARCHAR(50),
		ID_GROUP INT
	 )
 AS 
/* Return a table containing the rep first name, last name, ID, skill level, and group */
BEGIN
	INSERT @tblReps


	SELECT TOP 1	rep.ID,
			rep.FNAME,
			rep.LNAME,
			rep.IM,
			groups.GROUP_NAME,
			LTRIM(RTRIM( mgr1.FNAME + ' ' + mgr1.LNAME )) AS MGR1_NAME,
			rep.ID_MGR1,
			LTRIM(RTRIM( mgr2.FNAME + ' ' + mgr2.LNAME )) AS MGR2_NAME,
			rep.ID_MGR2,
			y.skill_level AS 'SKILL_LEVEL',
			CASE SKILL_LEVEL
				WHEN 1 THEN cfg.SB_ROUTE_SKILL_LBL1
				WHEN 2 THEN cfg.SB_ROUTE_SKILL_LBL2
				WHEN 3 THEN cfg.SB_ROUTE_SKILL_LBL3
				WHEN 4 THEN cfg.SB_ROUTE_SKILL_LBL4
			END AS 'SKILL_LEVEL_NAME',
			groups.ID AS 'ID_GROUP'
	FROM		CONFIG_INCIDENT AS cfg
	INNER JOIN	REPS AS rep ON rep.IS_AVAILABLE = 1 AND rep.PENDING_DELETION <> 1
	CROSS APPLY	fn_GetRepSkill(rep.[ID], @intCategoryID ) AS y
	INNER JOIN	REP_SKILLS AS repSkills ON repSkills.ID_REP = rep.ID
	LEFT JOIN	GROUPS AS groups ON groups.ID = rep.ID_DEFAULT_GROUP
	LEFT JOIN	REPS AS mgr1 ON mgr1.ID = rep.ID_MGR1
	LEFT JOIN	REPS AS mgr2 ON mgr2.ID = rep.ID_MGR2
	WHERE		EXISTS (SELECT * FROM fn_GetCategoryAncestry(@intCategoryID ) AS x WHERE x.ID = repSkills.ID_CATEGORY)
	ORDER BY	repSkills.SKILL_LEVEL,
			rep.FNAME,
			rep.LNAME,
			groups.GROUP_NAME

	RETURN
END

quote:

---

Originally posted by moonwalkercs

The line: (SELECT TOP (1) skill_level FROM fn_GetRepSkill(rep.[ID], @intCategoryID )) AS 'SKILL_LEVEL' --- is giving me the following error: Incorrect syntax near '.'

Any idea of what I could do to fix this? If I pass a hardcoded value instead of rep.[ID] it works fine.

---