macromedia.coldfusion.cfml_general_discussion
[Top] [All Lists]

Re: Dynamic ToolTip

Subject: Re: Dynamic ToolTip
From: "AppDeveloper"
Date: Thu, 2 Aug 2007 02:28:41 +0000 UTC
Newsgroups: macromedia.coldfusion.cfml_general_discussion
If you are using MS SQL server, you can use (execute) this User Defined 
Function in your SQL server to dynamically get the list of department(s) for 
each employee:

 CREATE FUNCTION dbo.fn_GetDepartmentNames (
     @Employee_ID       int
 )
 RETURNS varchar(3000)

 AS
 BEGIN
        DECLARE @Department_List varchar(2000)
        SET @Department_List = ''

        SELECT @Department_List = @Department_List + CASE WHEN @Department_List 
= '' 
THEN '' ELSE ', ' END 
                + LTRIM(RTRIM(dept.DeptName))
        FROM Employee emp
        JOIN Department dept ON emp.ID = dept.EmpID
        WHERE emp.ID = @Employee_ID
        ORDER BY emp.Name --or whatever you want to order by

     RETURN @Department_List

 END

 Then in your main query:

 SELECT emp.Name, DeptNames = dbo.fn_GetDepartmentNames(emp.ID)
 FROM Employee emp
 ORDER BY emp.Name --or whatever you want to order by

 If you output this query, you should get a list of department(s) for each row 
of employee in your result.

 <table border="1" cellpadding="1" cellspacing="0">
 <tr>
    <td>Employee Name</td>
 </tr>
 <CFOUTPUT QUERY="MyMainQuery">
    <tr>
       <td TITLE="#DeptNames#">#Name#</td> <!--- TITLE attribute is used here 
to display your Tool Tip, but this is IE ONLY. --->
    </tr>
 </CFOUTPUT>
 </table>



<Prev in Thread] Current Thread [Next in Thread>