Solved

Ideas on Cross search

Posted on 2008-10-20
47
304 Views
Last Modified: 2011-10-19
I need advice as to what route to take ...

We have an HR search using dynamic sql in a sproc.
The sproc uses a view to pull back all possible search criteria that might be need by user searching HR information.
Pulls back all Employee information fine.

On the other side we have the same dynamic sql in a sproc doing same sort of search from Intranet DB.
The sproc uses a table that is refreshed with data about meeting room locations and if they are available and contractors names and details.
if this information was stored in the new HR system we wouldnt need this cross search.

We are trying to work out the best way to do this .
Suggestions please on following:

Run a sproc that runs HR search and inserts into a temp table all results
This sproc also runs similar Intranet search and stores the ROOM and CONTRACTOR details in same temp table.
We then distinct this temp table as part of sproc

Then a Search sproc called StaffSearch - points to this temp table and using dynamic sql shows whats searched on to user.
Be it HR type details like employee information or Room/Contractor information.




0
Comment
Question by:mooriginal
  • 24
  • 23
47 Comments
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
Wow.  Lots going on here.  Let's start with some ideas.  First, I don't like putting things in temp tables then doing stuff with it.  Have you ever written/used Table Functions?  Below is a simple example, but is a concept that I use all the time when I need a "complicated" result set as a pseud-table.  You can do the same sort of thing you do with the temp table, but now you will have a sql "object" that you can treat like a normal selectable table.

{Table function}
CREATE FUNCTION dbo.udf_GetSalesEmployees
      (@FacilityID int)
RETURNS @table_variable TABLE (EmployeeID int, SalesTypeID int)
AS
      BEGIN
            INSERT INTO @table_variable
                SELECT EmployeeID, SalesTypeID from OneDataBaseTable

                UNION

                INSERT INTO @table_variable
                SELECT EmployeeID, SalesTypeID From AnotherDataBaseTable
                WHERE FacilityID = @FacilityID

                etc.

        RETURN

        END




Then, in your stored proc you can have something like:

CREATE Procedure dbo.usp_SelectSalesEmployees(@FacilityID int)

Select
    E.LastName,
    E.Firstname,
    st.SalesType,
    st.SalesTypeID
from
    dbo.udf_GetSalesEmployees(@FacilityID) as gse
    INNER JOIN Employee as E on E.EmployeeID = gse.EmployeeID
    INNER JOIN SalesType as st on st.SalesTypeID = gse.SalesTypeID
WHERE
   blah blah blah



So, as you can see, a Table function returns an object that SQL will treat as any other table that can be joined and referenced in the same way.  This is really powerful and allows you to centralize the logic for what that reference table contains.
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
Also, I don't like dynamic SQL very much.  Check out the comments toward the end of this post for some ideas of how to get around the need for dynamic sql.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23823728.html

0
 

Author Comment

by:mooriginal
Comment Utility
havent used a table function before
so interesting ...

So in other words run the select view i have as part of the table function create statement and populate the table through the function...?

How to run 2 different views in a table function then ...
what i mean is that HR view is showing one set of results - all employee data

Intranet view shows the Contractor and Rooms results

Id like the table function to then store both of results and run at the same time if possible or logical ?

Ive attached the HR view just for reference
SELECT  DISTINCT

 

        EmployeeID      =       e.EmployeeID,

        Status          =       es.EmployeeStatus,

        Start_Date      =       e.EmployeeStartDate,

        Emp_Name        =       e.Surname + ', ' + e.KnownAs,

        LoginID         =       e.WindowsUserName,

        UserID          =       e.DisplayEmployeeID,

        Extension       =       e.ExtNoUD,

        FFWMobile       =       e.FFWMobileUD,

        Email           =       e.WorkEmail,

        ProfitCentre    =       ch.HierarchyLevel3,

        Department      =       ch.HierarchyLevel4,

        EliteNumber     =       ejwrks.Elitenumberud,

        Secretary       =       sec.EmployeeDescNoID,

        Location        =       e.LocationUD,

        Position        =       ej1.PostID,

        WorksFor1       =       EWF1.EmployeeDescNoID,

        WorksFor2       =       EWF2.EmployeeDescNoID,

        WorksFor3       =       EWF3.EmployeeDescNoID,

        WorksFor4       =       EWF4.EmployeeDescNoID,

        WorksFor5       =       EWF5.EmployeeDescNoID,

        WorksFor        =       EBoss.EmployeeDescNoID,

        AboutMe         =       e.AboutMeud,

        Responsibilites =       cast(e.Responsibilitiesud as varchar(50))

--Responsibilites = convert(varchar(4000),e.Responsibilitiesud)
 
 

 

FROM    [Cascade].dbo.Employee          e

 

JOIN    [Cascade].dbo.Employee_Status   es  

ON      e.EmployeeID    =       es.EmployeeID

 

LEFT OUTER JOIN 

        (

                select  t1.JobTitle, t1.EmployeeID, t1.PostID

                from    [Cascade].dbo.EmployeeJobs      t1

                join    (

                                select  employeeId, min(Sequence) as sequence

                                from    [Cascade].dbo.EmployeeJobs

                                group by employeeID

                        )                               t2 

                on      t1.employeeid   =       t2.employeeID   

                and     t1.Sequence     =       t2.Sequence

        )                               ej1  

ON                      e.EmployeeID            =       ej1.EmployeeID

LEFT OUTER JOIN 

        [Cascade].dbo.Employee sec

ON      sec.EmployeeID  =       e.SecretaryNameUD 

 

JOIN    [Cascade].dbo.EmployeeJobs      ejwrks

ON      e.employeeid    =       ejwrks.employeeid

 

JOIN

        [Cascade].dbo.CompanyHierarchy   ch

ON      ejwrks.HierarchyNodeUD  =   ch.HierarchyNode

 

JOIN 

        [Cascade].dbo.Employee eBoss

ON      EBoss.EmployeeID =               ejwrks.WorksForEmployeeID

 

LEFT OUTER JOIN

        [Cascade].dbo.Employee EWF1

ON      EWF1.EmployeeID  =               e.Manager1ud

 

LEFT OUTER JOIN

                [Cascade].dbo.Employee EWF2

ON              EWF2.EmployeeID                 =               e.Manager2ud

 

LEFT OUTER JOIN

                [Cascade].dbo.Employee EWF3

ON              EWF3.EmployeeID                 =               e.Manager3ud

 

LEFT OUTER JOIN

                [Cascade].dbo.Employee EWF4

ON              EWF4.EmployeeID                 =               e.Manager4ud

 

LEFT OUTER JOIN

                [Cascade].dbo.Employee EWF5

ON              EWF5.EmployeeID                 =               e.Manager5ud

 

WHERE   es.EmployeeStatus						=       'Current'  

AND     ch.[InUse?]								=		'Live'

Open in new window

0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
Ultimately you want to end up with one table of information from which you perform a select...correct?

So, what are the critical parts of that table?  Are the Room and Contractor details a one to one relationship with the employees?
0
 

Author Comment

by:mooriginal
Comment Utility
yes your right one table with all results

the paths are difficult...
a user would do a search for all of the above criteria - but rooms and contractor are not stored in the HR database...
So the intranet system did the HR view but with addition of contractor and rooms.

We dont want to use the intranet system going forward but we have to cause of those 2 areas.

But really the only thing to join on would be e.loginID as that thats also captured in the intranet search and is the same regardless of HR or Intranet

The HR system doesnt store a loginID for the contractors or rooms...
So couldnt use it to join on ....
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
Let's go simple here.

As I understand your question, you want a table that holds employee information AND contractor information AND Room information - those sound like three different things to me.  I can see a case for Contractors, but rooms?  Let's say a row contains the following:

     Name                    Status
1.  Public, John Q.      1
2.  Public, Jane Q.      0


In this same table you want to list contractors:

     Name                    Status
1.  Sixpack, Joe          1
2.  Temp, Nancy         1


Where I'm getting lost is your requirement to list Rooms.  Do you want a row with "phoney" people in it that are actually rooms?

Name                   Status
1.  Conf. Room B       1
2.  Conf. Room G       0


0
 

Author Comment

by:mooriginal
Comment Utility
yup good point ill pick this up tomorrow and look at logic too..
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
So...one thought.  Maybe you don't need to roll all this up into one SUPER query.  Maybe you are needing two.  Or three....
0
 

Author Comment

by:mooriginal
Comment Utility
well here is the logic

the Intranet DB search side of things is referenced by 2 criteria
1. userID column - even though its not a user - conference/meeting rooms are given a UID = userID.
The HR DB only stores userID for employees.
2. Status column - stores value to reference that its a person or equipment - such as conference room etc. Eg. status 5 = conference room, status 6 = contractors

But to do search for a conference room - a person would type in search box ' conference room' which would 'like' match from the UserName column.
The userid is never used to do the search.
To do a search for contractor - its done through the UserName again - but due to legacy - this contractor although a employee is not stored in the new HR database hence why we still need to point to the Intranet search ...



0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
OK.  So, this is a framework that can work for you.

Table 1 - Employee:  UserID, Status, Name
Table 2 - Contractor:  UserID, Status, Name
Table 3 - Conference Rooms:  UserID, Status, Name


CREATE FUNCTION dbo.udf_GetResources
RETURNS @table_variable TABLE (UserID varchar(20), Status int, Name varchar(100))
AS
      BEGIN
            INSERT INTO @table_variable
                SELECT UserID, 1, EmployeeName FROM Employee

                UNION

                SELECT ContractorID, 2, ContractorName  FROM Contractor


                UNION

                SELECT ConferenceRoomID, 3, ConferenceRoomName FROM ConferenceRoom


        RETURN

        END





So, that would be the function that pulls together all the data into one "unified" table.

You could then write a stored procedure like:


CREATE Procedure SearchResources (@UserID varchar(20), @Status int)

SELECT
    t.UserID,
    t.Status,
    t.Username
FROM
    dbo.udf_GetResources as t
WHERE
    (@UserID is NULL or (t.UserID LIKE '%' + @UserID + '%' AND t.Status = 3)  --where 3 is conference room
    AND (@Status is null OR (t.Status = @Status)





Of course there are a lot more columns and complexity, but this is the basic idea.  The function isn't really necessary if you do no filtering - you could just as easily make a view, but if you care which records are returned from the three separate source, then the table function makes a lot of sense.
0
 

Author Comment

by:mooriginal
Comment Utility
thanks for all that
So.....
Ive included the simple sql for the search from the intranet to flesh it out with actual columns

Ive also attached the view for the HR Search...
And Sproc for dynamic sql as is but now pointing to udf ...

Can I still use the syntax for the udf create from these 2 views ?
considering the intranet view doesnt pull back as many columns as the HR view ?


CREATE VIEW view_Staffsearch_Intranet

AS

SELECT  DISTINCT

 

        Intra_Status     =       tbl.Status,

        Intra_Emp_Name   =       tbl.Name,

        Intra_UserID     =       tbl.UserID,

        Intr_Extension   =       tbl.Extension,

        Intra_Department =		 tbl.Department,

        Intra_Location   =       tbl.Location

        
 

FROM	 NEMESIS_LINKED.IntranetMA1.dbo.tblUsers tbl
 

WHERE	 tbl.status = 5 OR tbl.status = 6

Open in new window

HR-view.txt
HR-StaffSearch-Sproc.txt
0
 
LVL 16

Accepted Solution

by:
Steve Krile earned 500 total points
Comment Utility
Certainly. The point of putting the whole thing in a Table Function, is you can UNION together several different selects, and the result is treated as one big table.  I've attached a go at putting this all together.  Have a look and let me know how it works for you.
CombinedSolution.txt
0
 

Author Comment

by:mooriginal
Comment Utility
listen youve been a great help
ill award the points and post back tomorrow on the thread to see where i got to with it ...
0
 

Author Comment

by:mooriginal
Comment Utility
Hope you get a chance to look at this again

getting the following error message to the function create

Msg 102, Level 15, State 1, Procedure udf_GetResources, Line 1
Incorrect syntax near 'RETURNS'.
CREATE FUNCTION dbo.udf_GetResources RETURNS @table_variable TABLE
 

(

        EmployeeID 		 nvarchar(1000),   

        Status           nvarchar(1000), 

        Start_Date       nvarchar(1000), 

        Emp_Name         nvarchar(1000), 

        LoginID          nvarchar(1000), 

        UserID           nvarchar(1000), 

        Extension        nvarchar(1000), 

        FFWMobile        nvarchar(1000), 

        Email            nvarchar(1000), 

        ProfitCentre     nvarchar(1000), 

        Department       nvarchar(1000), 

        EliteNumber      nvarchar(1000), 

        Secretary        nvarchar(1000), 

        Location         nvarchar(1000), 

        Position         nvarchar(1000), 

        WorksFor1        nvarchar(1000), 

        WorksFor2        nvarchar(1000), 

        WorksFor3        nvarchar(1000), 

        WorksFor4        nvarchar(1000), 

        WorksFor5        nvarchar(1000), 

        WorksFor         nvarchar(1000), 

        AboutMe          nvarchar(1000), 

        Responsibilites  nvarchar(1000)

)

AS

	BEGIN

		SELECT  DISTINCT

			EmployeeID      =       e.EmployeeID,

			Status          =       es.EmployeeStatus,

			Start_Date      =       e.EmployeeStartDate,

			Emp_Name        =       e.Surname + ', ' + e.KnownAs,

			LoginID         =       e.WindowsUserName,

			UserID          =       e.DisplayEmployeeID,

			Extension       =       e.ExtNoUD,

			FFWMobile       =       e.FFWMobileUD,

			Email           =       e.WorkEmail,

			ProfitCentre    =       ch.HierarchyLevel3,

			Department      =       ch.HierarchyLevel4,

			EliteNumber     =       ejwrks.Elitenumberud,

			Secretary       =       sec.EmployeeDescNoID,

			Location        =       e.LocationUD,

			Position        =       ej1.PostID,

			WorksFor1       =       EWF1.EmployeeDescNoID,

			WorksFor2       =       EWF2.EmployeeDescNoID,

			WorksFor3       =       EWF3.EmployeeDescNoID,

			WorksFor4       =       EWF4.EmployeeDescNoID,

			WorksFor5       =       EWF5.EmployeeDescNoID,

			WorksFor        =       EBoss.EmployeeDescNoID,

			AboutMe         =       cast(e.AboutMeud as varchar(50)),

			Responsibilites =       cast(e.Responsibilitiesud as varchar(50))
 

		 

		FROM    [Cascade].dbo.Employee          e

			JOIN    [Cascade].dbo.Employee_Status   es  ON      e.EmployeeID    =       es.EmployeeID

			LEFT OUTER JOIN 

				(

					select  t1.JobTitle, t1.EmployeeID, t1.PostID

					from    [Cascade].dbo.EmployeeJobs      t1

					join    (

							select  employeeId, min(Sequence) as sequence

							from    [Cascade].dbo.EmployeeJobs

							group by employeeID

						)                               t2 

					on      t1.employeeid   =       t2.employeeID   

					and     t1.Sequence     =       t2.Sequence

				)                               ej1  

			ON                      e.EmployeeID            =       ej1.EmployeeID

			LEFT OUTER JOIN [Cascade].dbo.Employee sec ON sec.EmployeeID  =       e.SecretaryNameUD 

			JOIN    [Cascade].dbo.EmployeeJobs ejwrks ON e.employeeid    =       ejwrks.employeeid

			JOIN	[Cascade].dbo.CompanyHierarchy   ch ON ejwrks.HierarchyNodeUD  =   ch.HierarchyNode 

			JOIN    [Cascade].dbo.Employee eBoss ON EBoss.EmployeeID = ejwrks.WorksForEmployeeID

			LEFT OUTER JOIN  [Cascade].dbo.Employee EWF1 ON EWF1.EmployeeID  = e.Manager1ud

			LEFT OUTER JOIN [Cascade].dbo.Employee EWF2 ON EWF2.EmployeeID = e.Manager2ud

			LEFT OUTER JOIN [Cascade].dbo.Employee EWF3 ON EWF3.EmployeeID = e.Manager3ud

			LEFT OUTER JOIN [Cascade].dbo.Employee EWF4 ON EWF4.EmployeeID = e.Manager4ud

			LEFT OUTER JOIN [Cascade].dbo.Employee EWF5 ON EWF5.EmployeeID = e.Manager5ud

		WHERE   

			es.EmployeeStatus = 'Current'  

			AND ch.[InUse?]	= 'Live'
 
 

		UNION

		

		SELECT  DISTINCT

			EmployeeID      =       NULL,

			Status          =       tbl.Status,

			Start_Date      =       NULL,

			Emp_Name        =       tbl.Name,

			LoginID         =       NULL,

			UserID          =       tbl.UserID,

			Extension       =       tbl.Extension,

			FFWMobile       =       NULL,

			Email           =       NULL,

			ProfitCentre    =       NULL,

			Department      =       tbl.Department,

			EliteNumber     =       NULL,

			Secretary       =       NULL,

			Location        =       tbl.Location,

			Position        =       NULL,

			WorksFor1       =       NULL,

			WorksFor2       =       NULL,

			WorksFor3       =       NULL,

			WorksFor4       =       NULL,

			WorksFor5       =       NULL,

			WorksFor        =       NULL,

			AboutMe         =       NULL,

			Responsibilites =       NULL
 

		        

		 

		FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl

		 

		WHERE    tbl.status = 5 OR tbl.status = 6
 
 
 

		RETURN

	END

	

	

Open in new window

0
 

Author Comment

by:mooriginal
Comment Utility
so tried this and then got another error message

Msg 156, Level 15, State 1, Procedure udf_GetResources, Line 32
Incorrect syntax near the keyword 'BEGIN'.



CREATE FUNCTION dbo.udf_GetResources (@table_variable varchar)
 

RETURNS TABLE
 

(

        EmployeeID 		 nvarchar(1000),   

        Status           nvarchar(1000), 

        Start_Date       nvarchar(1000), 

        Emp_Name         nvarchar(1000), 

        LoginID          nvarchar(1000), 

        UserID           nvarchar(1000), 

        Extension        nvarchar(1000), 

        FFWMobile        nvarchar(1000), 

        Email            nvarchar(1000), 

        ProfitCentre     nvarchar(1000), 

        Department       nvarchar(1000), 

        EliteNumber      nvarchar(1000), 

        Secretary        nvarchar(1000), 

        Location         nvarchar(1000), 

        Position         nvarchar(1000), 

        WorksFor1        nvarchar(1000), 

        WorksFor2        nvarchar(1000), 

        WorksFor3        nvarchar(1000), 

        WorksFor4        nvarchar(1000), 

        WorksFor5        nvarchar(1000), 

        WorksFor         nvarchar(1000), 

        AboutMe          nvarchar(1000), 

        Responsibilites  nvarchar(1000)

)

AS

	BEGIN

		SELECT  DISTINCT

			EmployeeID      =       e.EmployeeID,

			Status          =       es.EmployeeStatus,

			Start_Date      =       e.EmployeeStartDate,

			Emp_Name        =       e.Surname + ', ' + e.KnownAs,

			LoginID         =       e.WindowsUserName,

			UserID          =       e.DisplayEmployeeID,

			Extension       =       e.ExtNoUD,

			FFWMobile       =       e.FFWMobileUD,

			Email           =       e.WorkEmail,

			ProfitCentre    =       ch.HierarchyLevel3,

			Department      =       ch.HierarchyLevel4,

			EliteNumber     =       ejwrks.Elitenumberud,

			Secretary       =       sec.EmployeeDescNoID,

			Location        =       e.LocationUD,

			Position        =       ej1.PostID,

			WorksFor1       =       EWF1.EmployeeDescNoID,

			WorksFor2       =       EWF2.EmployeeDescNoID,

			WorksFor3       =       EWF3.EmployeeDescNoID,

			WorksFor4       =       EWF4.EmployeeDescNoID,

			WorksFor5       =       EWF5.EmployeeDescNoID,

			WorksFor        =       EBoss.EmployeeDescNoID,

			AboutMe         =       cast(e.AboutMeud as varchar(50)),

			Responsibilites =       cast(e.Responsibilitiesud as varchar(50))
 

		 

		FROM    [Cascade].dbo.Employee          e

			JOIN    [Cascade].dbo.Employee_Status   es  ON      e.EmployeeID    =       es.EmployeeID

			LEFT OUTER JOIN 

				(

					select  t1.JobTitle, t1.EmployeeID, t1.PostID

					from    [Cascade].dbo.EmployeeJobs      t1

					join    (

							select  employeeId, min(Sequence) as sequence

							from    [Cascade].dbo.EmployeeJobs

							group by employeeID

						)                               t2 

					on      t1.employeeid   =       t2.employeeID   

					and     t1.Sequence     =       t2.Sequence

				)                               ej1  

			ON                      e.EmployeeID            =       ej1.EmployeeID

			LEFT OUTER JOIN [Cascade].dbo.Employee sec ON sec.EmployeeID  =       e.SecretaryNameUD 

			JOIN    [Cascade].dbo.EmployeeJobs ejwrks ON e.employeeid    =       ejwrks.employeeid

			JOIN	[Cascade].dbo.CompanyHierarchy   ch ON ejwrks.HierarchyNodeUD  =   ch.HierarchyNode 

			JOIN    [Cascade].dbo.Employee eBoss ON EBoss.EmployeeID = ejwrks.WorksForEmployeeID

			LEFT OUTER JOIN  [Cascade].dbo.Employee EWF1 ON EWF1.EmployeeID  = e.Manager1ud

			LEFT OUTER JOIN [Cascade].dbo.Employee EWF2 ON EWF2.EmployeeID = e.Manager2ud

			LEFT OUTER JOIN [Cascade].dbo.Employee EWF3 ON EWF3.EmployeeID = e.Manager3ud

			LEFT OUTER JOIN [Cascade].dbo.Employee EWF4 ON EWF4.EmployeeID = e.Manager4ud

			LEFT OUTER JOIN [Cascade].dbo.Employee EWF5 ON EWF5.EmployeeID = e.Manager5ud

		WHERE   

			es.EmployeeStatus = 'Current'  

			AND ch.[InUse?]	= 'Live'
 
 

		UNION

		

		SELECT  DISTINCT

			EmployeeID      =       NULL,

			Status          =       tbl.Status,

			Start_Date      =       NULL,

			Emp_Name        =       tbl.Name,

			LoginID         =       NULL,

			UserID          =       tbl.UserID,

			Extension       =       tbl.Extension,

			FFWMobile       =       NULL,

			Email           =       NULL,

			ProfitCentre    =       NULL,

			Department      =       tbl.Department,

			EliteNumber     =       NULL,

			Secretary       =       NULL,

			Location        =       tbl.Location,

			Position        =       NULL,

			WorksFor1       =       NULL,

			WorksFor2       =       NULL,

			WorksFor3       =       NULL,

			WorksFor4       =       NULL,

			WorksFor5       =       NULL,

			WorksFor        =       NULL,

			AboutMe         =       NULL,

			Responsibilites =       NULL
 

		        

		 

		FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl

		 

		WHERE    tbl.status = 5 OR tbl.status = 6
 
 
 

		RETURN

		

	END

	

Open in new window

0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
The basic syntax is:

CREATE FUNCTION [dbo].[FunctionName]
(parameter list)
RETURNS @table_variable TABLE (all your table elements)
AS

   BEGIN
          INSERT INTO @table_variable
           .
           .
           .
    RETURN
    END




So, in you case you should have:

CREATE FUNCTION [dbo].[udf_GetResources]
RETURNS @table_variable TABLE (
        EmployeeID  nvarchar(1000),  
        Status          nvarchar(1000),
        Start_Date   nvarchar(1000),
        Emp_Name  nvarchar(1000),
        LoginID          nvarchar(1000),
        UserID           nvarchar(1000),
        Extension        nvarchar(1000),
        FFWMobile        nvarchar(1000),
        Email            nvarchar(1000),
        ProfitCentre     nvarchar(1000),
        Department       nvarchar(1000),
        EliteNumber      nvarchar(1000),
        Secretary        nvarchar(1000),
        Location         nvarchar(1000),
        Position         nvarchar(1000),
        WorksFor1        nvarchar(1000),
        WorksFor2        nvarchar(1000),
        WorksFor3        nvarchar(1000),
        WorksFor4        nvarchar(1000),
        WorksFor5        nvarchar(1000),
        WorksFor         nvarchar(1000),
        AboutMe          nvarchar(1000),
        Responsibilites  nvarchar(1000)
)
AS
      BEGIN
            SELECT  DISTINCT
                  EmployeeID      =       e.EmployeeID,
                  Status          =       es.EmployeeStatus,
                  Start_Date      =       e.EmployeeStartDate,
                  Emp_Name        =       e.Surname + ', ' + e.KnownAs,
                  LoginID         =       e.WindowsUserName,
                  UserID          =       e.DisplayEmployeeID,
                  Extension       =       e.ExtNoUD,
                  FFWMobile       =       e.FFWMobileUD,
                  Email           =       e.WorkEmail,
                  ProfitCentre    =       ch.HierarchyLevel3,
                  Department      =       ch.HierarchyLevel4,
                  EliteNumber     =       ejwrks.Elitenumberud,
                  Secretary       =       sec.EmployeeDescNoID,
                  Location        =       e.LocationUD,
                  Position        =       ej1.PostID,
                  WorksFor1       =       EWF1.EmployeeDescNoID,
                  WorksFor2       =       EWF2.EmployeeDescNoID,
                  WorksFor3       =       EWF3.EmployeeDescNoID,
                  WorksFor4       =       EWF4.EmployeeDescNoID,
                  WorksFor5       =       EWF5.EmployeeDescNoID,
                  WorksFor        =       EBoss.EmployeeDescNoID,
                  AboutMe         =       cast(e.AboutMeud as varchar(50)),
                  Responsibilites =       cast(e.Responsibilitiesud as varchar(50))
 
             
            FROM    [Cascade].dbo.Employee          e
                  JOIN    [Cascade].dbo.Employee_Status   es  ON      e.EmployeeID    =       es.EmployeeID
                  LEFT OUTER JOIN
                        (
                              select  t1.JobTitle, t1.EmployeeID, t1.PostID
                              from    [Cascade].dbo.EmployeeJobs      t1
                              join    (
                                          select  employeeId, min(Sequence) as sequence
                                          from    [Cascade].dbo.EmployeeJobs
                                          group by employeeID
                                    )                               t2
                              on      t1.employeeid   =       t2.employeeID  
                              and     t1.Sequence     =       t2.Sequence
                        )                               ej1  
                  ON                      e.EmployeeID            =       ej1.EmployeeID
                  LEFT OUTER JOIN [Cascade].dbo.Employee sec ON sec.EmployeeID  =       e.SecretaryNameUD
                  JOIN    [Cascade].dbo.EmployeeJobs ejwrks ON e.employeeid    =       ejwrks.employeeid
                  JOIN      [Cascade].dbo.CompanyHierarchy   ch ON ejwrks.HierarchyNodeUD  =   ch.HierarchyNode
                  JOIN    [Cascade].dbo.Employee eBoss ON EBoss.EmployeeID = ejwrks.WorksForEmployeeID
                  LEFT OUTER JOIN  [Cascade].dbo.Employee EWF1 ON EWF1.EmployeeID  = e.Manager1ud
                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF2 ON EWF2.EmployeeID = e.Manager2ud
                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF3 ON EWF3.EmployeeID = e.Manager3ud
                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF4 ON EWF4.EmployeeID = e.Manager4ud
                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF5 ON EWF5.EmployeeID = e.Manager5ud
            WHERE  
                  es.EmployeeStatus = 'Current'  
                  AND ch.[InUse?]      = 'Live'
 
 
            UNION
            
            SELECT  DISTINCT
                  EmployeeID      =       NULL,
                  Status          =       tbl.Status,
                  Start_Date      =       NULL,
                  Emp_Name        =       tbl.Name,
                  LoginID         =       NULL,
                  UserID          =       tbl.UserID,
                  Extension       =       tbl.Extension,
                  FFWMobile       =       NULL,
                  Email           =       NULL,
                  ProfitCentre    =       NULL,
                  Department      =       tbl.Department,
                  EliteNumber     =       NULL,
                  Secretary       =       NULL,
                  Location        =       tbl.Location,
                  Position        =       NULL,
                  WorksFor1       =       NULL,
                  WorksFor2       =       NULL,
                  WorksFor3       =       NULL,
                  WorksFor4       =       NULL,
                  WorksFor5       =       NULL,
                  WorksFor        =       NULL,
                  AboutMe         =       NULL,
                  Responsibilites =       NULL
 
                   
             
            FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl
             
            WHERE    tbl.status = 5 OR tbl.status = 6
 
 
 
            RETURN
      END
0
 

Author Comment

by:mooriginal
Comment Utility
thanks
Ive tried your syntax as you mention but i get
Incorrect syntax near 'RETURNS'.

should it not be like this ?
with insert into after the begin ?
CREATE FUNCTION [dbo].[udf_GetResources]

RETURNS @table_variable TABLE (

        EmployeeID  nvarchar(1000),  

        Status          nvarchar(1000),

        Start_Date   nvarchar(1000),

        Emp_Name  nvarchar(1000),

        LoginID          nvarchar(1000),

        UserID           nvarchar(1000),

        Extension        nvarchar(1000),

        FFWMobile        nvarchar(1000),

        Email            nvarchar(1000),

        ProfitCentre     nvarchar(1000),

        Department       nvarchar(1000),

        EliteNumber      nvarchar(1000),

        Secretary        nvarchar(1000),

        Location         nvarchar(1000),

        Position         nvarchar(1000),

        WorksFor1        nvarchar(1000),

        WorksFor2        nvarchar(1000),

        WorksFor3        nvarchar(1000),

        WorksFor4        nvarchar(1000),

        WorksFor5        nvarchar(1000),

        WorksFor         nvarchar(1000),

        AboutMe          nvarchar(1000),

        Responsibilites  nvarchar(1000)

)

AS

      BEGIN

          INSERT INTO @table_variable

            SELECT  DISTINCT

                  EmployeeID      =       e.EmployeeID,

                  Status          =       es.EmployeeStatus,

                  Start_Date      =       e.EmployeeStartDate,

                  Emp_Name        =       e.Surname + ', ' + e.KnownAs,

                  LoginID         =       e.WindowsUserName,

                  UserID          =       e.DisplayEmployeeID,

                  Extension       =       e.ExtNoUD,

                  FFWMobile       =       e.FFWMobileUD,

                  Email           =       e.WorkEmail,

                  ProfitCentre    =       ch.HierarchyLevel3,

                  Department      =       ch.HierarchyLevel4,

                  EliteNumber     =       ejwrks.Elitenumberud,

                  Secretary       =       sec.EmployeeDescNoID,

                  Location        =       e.LocationUD,

                  Position        =       ej1.PostID,

                  WorksFor1       =       EWF1.EmployeeDescNoID,

                  WorksFor2       =       EWF2.EmployeeDescNoID,

                  WorksFor3       =       EWF3.EmployeeDescNoID,

                  WorksFor4       =       EWF4.EmployeeDescNoID,

                  WorksFor5       =       EWF5.EmployeeDescNoID,

                  WorksFor        =       EBoss.EmployeeDescNoID,

                  AboutMe         =       cast(e.AboutMeud as varchar(50)),

                  Responsibilites =       cast(e.Responsibilitiesud as varchar(50))

 

             

            FROM    [Cascade].dbo.Employee          e

                  JOIN    [Cascade].dbo.Employee_Status   es  ON      e.EmployeeID    =       es.EmployeeID

                  LEFT OUTER JOIN

                        (

                              select  t1.JobTitle, t1.EmployeeID, t1.PostID

                              from    [Cascade].dbo.EmployeeJobs      t1

                              join    (

                                          select  employeeId, min(Sequence) as sequence

                                          from    [Cascade].dbo.EmployeeJobs

                                          group by employeeID

                                    )                               t2

                              on      t1.employeeid   =       t2.employeeID  

                              and     t1.Sequence     =       t2.Sequence

                        )                               ej1  

                  ON                      e.EmployeeID            =       ej1.EmployeeID

                  LEFT OUTER JOIN [Cascade].dbo.Employee sec ON sec.EmployeeID  =       e.SecretaryNameUD

                  JOIN    [Cascade].dbo.EmployeeJobs ejwrks ON e.employeeid    =       ejwrks.employeeid

                  JOIN      [Cascade].dbo.CompanyHierarchy   ch ON ejwrks.HierarchyNodeUD  =   ch.HierarchyNode

                  JOIN    [Cascade].dbo.Employee eBoss ON EBoss.EmployeeID = ejwrks.WorksForEmployeeID

                  LEFT OUTER JOIN  [Cascade].dbo.Employee EWF1 ON EWF1.EmployeeID  = e.Manager1ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF2 ON EWF2.EmployeeID = e.Manager2ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF3 ON EWF3.EmployeeID = e.Manager3ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF4 ON EWF4.EmployeeID = e.Manager4ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF5 ON EWF5.EmployeeID = e.Manager5ud

            WHERE  

                  es.EmployeeStatus = 'Current'  

                  AND ch.[InUse?]      = 'Live'

 

 

            UNION

            

            SELECT  DISTINCT

                  EmployeeID      =       NULL,

                  Status          =       tbl.Status,

                  Start_Date      =       NULL,

                  Emp_Name        =       tbl.Name,

                  LoginID         =       NULL,

                  UserID          =       tbl.UserID,

                  Extension       =       tbl.Extension,

                  FFWMobile       =       NULL,

                  Email           =       NULL,

                  ProfitCentre    =       NULL,

                  Department      =       tbl.Department,

                  EliteNumber     =       NULL,

                  Secretary       =       NULL,

                  Location        =       tbl.Location,

                  Position        =       NULL,

                  WorksFor1       =       NULL,

                  WorksFor2       =       NULL,

                  WorksFor3       =       NULL,

                  WorksFor4       =       NULL,

                  WorksFor5       =       NULL,

                  WorksFor        =       NULL,

                  AboutMe         =       NULL,

                  Responsibilites =       NULL

 

                   

             

            FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl

             

            WHERE    tbl.status = 5 OR tbl.status = 6

 

 

 

            RETURN

      END

Open in new window

0
 

Author Comment

by:mooriginal
Comment Utility
also stupid stupid question
how do i try the function - whats the syntax for a test run of it ?
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
Oy....at the very beginning:

AS

   BEGIN

       INSERT INTO @table_variable
                    SELECT
                      .
                      .
                      .
                      UNION





As for testing.  Just open query analyzer or Sql Server Managment studio and type:

select * from [dbo].[GetResources]
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
Wait....I just noticed something.


Try this at the top:

CREATE FUNCTION [dbo].[udf_GetResources] ()  <-----add this empty bracket




The great thing about Table Functions is you can parameratize them, passing in filter values that will stream line the amount of data returned.  Then, in your Stored Proc further filter them based on other tables you want to join.  So, in your example, you aren't passing in any variables - just leave empty brackets.
0
 

Author Comment

by:mooriginal
Comment Utility
thanks dude not working though

ive tried this and got error message

Msg 444, Level 16, State 2, Procedure udf_GetResources_MA1, Line 30
Select statements included within a function cannot return data to a client.






CREATE FUNCTION [dbo].[udf_GetResources_MA1] () 

RETURNS @table_variable TABLE (

        EmployeeID  nvarchar(1000),  

        Status          nvarchar(1000),

        Start_Date   nvarchar(1000),

        Emp_Name  nvarchar(1000),

        LoginID          nvarchar(1000),

        UserID           nvarchar(1000),

        Extension        nvarchar(1000),

        FFWMobile        nvarchar(1000),

        Email            nvarchar(1000),

        ProfitCentre     nvarchar(1000),

        Department       nvarchar(1000),

        EliteNumber      nvarchar(1000),

        Secretary        nvarchar(1000),

        Location         nvarchar(1000),

        Position         nvarchar(1000),

        WorksFor1        nvarchar(1000),

        WorksFor2        nvarchar(1000),

        WorksFor3        nvarchar(1000),

        WorksFor4        nvarchar(1000),

        WorksFor5        nvarchar(1000),

        WorksFor         nvarchar(1000),

        AboutMe          nvarchar(1000),

        Responsibilites  nvarchar(1000)

)

AS

      BEGIN

            SELECT  DISTINCT

                  EmployeeID      =       e.EmployeeID,

                  Status          =       es.EmployeeStatus,

                  Start_Date      =       e.EmployeeStartDate,

                  Emp_Name        =       e.Surname + ', ' + e.KnownAs,

                  LoginID         =       e.WindowsUserName,

                  UserID          =       e.DisplayEmployeeID,

                  Extension       =       e.ExtNoUD,

                  FFWMobile       =       e.FFWMobileUD,

                  Email           =       e.WorkEmail,

                  ProfitCentre    =       ch.HierarchyLevel3,

                  Department      =       ch.HierarchyLevel4,

                  EliteNumber     =       ejwrks.Elitenumberud,

                  Secretary       =       sec.EmployeeDescNoID,

                  Location        =       e.LocationUD,

                  Position        =       ej1.PostID,

                  WorksFor1       =       EWF1.EmployeeDescNoID,

                  WorksFor2       =       EWF2.EmployeeDescNoID,

                  WorksFor3       =       EWF3.EmployeeDescNoID,

                  WorksFor4       =       EWF4.EmployeeDescNoID,

                  WorksFor5       =       EWF5.EmployeeDescNoID,

                  WorksFor        =       EBoss.EmployeeDescNoID,

                  AboutMe         =       cast(e.AboutMeud as varchar(50)),

                  Responsibilites =       cast(e.Responsibilitiesud as varchar(50))

 

             

            FROM    [Cascade].dbo.Employee          e

                  JOIN    [Cascade].dbo.Employee_Status   es  ON      e.EmployeeID    =       es.EmployeeID

                  LEFT OUTER JOIN

                        (

                              select  t1.JobTitle, t1.EmployeeID, t1.PostID

                              from    [Cascade].dbo.EmployeeJobs      t1

                              join    (

                                          select  employeeId, min(Sequence) as sequence

                                          from    [Cascade].dbo.EmployeeJobs

                                          group by employeeID

                                    )                               t2

                              on      t1.employeeid   =       t2.employeeID  

                              and     t1.Sequence     =       t2.Sequence

                        )                               ej1  

                  ON                      e.EmployeeID            =       ej1.EmployeeID

                  LEFT OUTER JOIN [Cascade].dbo.Employee sec ON sec.EmployeeID  =       e.SecretaryNameUD

                  JOIN    [Cascade].dbo.EmployeeJobs ejwrks ON e.employeeid    =       ejwrks.employeeid

                  JOIN      [Cascade].dbo.CompanyHierarchy   ch ON ejwrks.HierarchyNodeUD  =   ch.HierarchyNode

                  JOIN    [Cascade].dbo.Employee eBoss ON EBoss.EmployeeID = ejwrks.WorksForEmployeeID

                  LEFT OUTER JOIN  [Cascade].dbo.Employee EWF1 ON EWF1.EmployeeID  = e.Manager1ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF2 ON EWF2.EmployeeID = e.Manager2ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF3 ON EWF3.EmployeeID = e.Manager3ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF4 ON EWF4.EmployeeID = e.Manager4ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF5 ON EWF5.EmployeeID = e.Manager5ud

            WHERE  

                  es.EmployeeStatus = 'Current'  

                  AND ch.[InUse?]      = 'Live'

 

 

            UNION

            

            SELECT  DISTINCT

                  EmployeeID      =       NULL,

                  Status          =       tbl.Status,

                  Start_Date      =       NULL,

                  Emp_Name        =       tbl.Name,

                  LoginID         =       NULL,

                  UserID          =       tbl.UserID,

                  Extension       =       tbl.Extension,

                  FFWMobile       =       NULL,

                  Email           =       NULL,

                  ProfitCentre    =       NULL,

                  Department      =       tbl.Department,

                  EliteNumber     =       NULL,

                  Secretary       =       NULL,

                  Location        =       tbl.Location,

                  Position        =       NULL,

                  WorksFor1       =       NULL,

                  WorksFor2       =       NULL,

                  WorksFor3       =       NULL,

                  WorksFor4       =       NULL,

                  WorksFor5       =       NULL,

                  WorksFor        =       NULL,

                  AboutMe         =       NULL,

                  Responsibilites =       NULL

 

                   

             

            FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl

             

            WHERE    tbl.status = 5 OR tbl.status = 6

 

 

 

            RETURN

      END

Open in new window

0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
OK, let's try simpler.  I have a sneaking suspicion that your first SELECT statement has something in it that doesn't jibe with SQL's logic.


What happens if you do something simple like this:

CREATE FUNCTION [dbo].[udf_GetResources_MA1] ()
RETURNS @table_variable TABLE (
        EmployeeID  nvarchar(1000),  
        Status          nvarchar(1000),
        Start_Date   nvarchar(1000)
)

BEGIN
            SELECT  DISTINCT
                  EmployeeID      =       e.EmployeeID,
                  Status          =       es.EmployeeStatus,
                  Start_Date      =       e.EmployeeStartDate
            FROM
                  [Cascade].dbo.Employee as E,
                  INNER JOIN [Cascade].dbo.Employee_status as es ON e.EmployeeID = es.EmpoyeeID
            WHERE  
                  es.EmployeeStatus = 'Current'

            UNION

             SELECT  DISTINCT
                  EmployeeID      =       NULL,
                  Status          =       tbl.Status,
                  Start_Date      =       NULL
            FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl
            WHERE    tbl.status = 5 OR tbl.status = 6

            RETURN

         END




0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
Oooh.  I think I've found your problem.  You have a left outer join with multiple items in the select:

                  LEFT OUTER JOIN
                        (
                              select  t1.JobTitle, t1.EmployeeID, t1.PostID
                              from    [Cascade].dbo.EmployeeJobs      t1
                              join    (
                                          select  employeeId, min(Sequence) as sequence
                                          from    [Cascade].dbo.EmployeeJobs
                                          group by employeeID
                                    )                               t2
                              on      t1.employeeid   =       t2.employeeID  
                              and     t1.Sequence     =       t2.Sequence
                        )

This doesn't look *right* to me.  What's going on with this join?

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mooriginal
Comment Utility
this join is needed as it about showing the most current job title for a employee which can be many for employee name so to unique it it must be narrowed down by the sequence field

which decreases everytime there is a change
so hence to show current job title sequence is set to min...

thats has to be in ...
0
 

Author Comment

by:mooriginal
Comment Utility
also ive tried this and its compiled ok
CREATE FUNCTION dbo.udf_GetResources (@table_variable varchar)

RETURNS @res TABLE

(

        EmployeeID       nvarchar(1000),   

        Status           nvarchar(1000), 

        Start_Date       nvarchar(1000), 

        Emp_Name         nvarchar(1000), 

        LoginID          nvarchar(1000), 

        UserID           nvarchar(1000), 

        Extension        nvarchar(1000), 

        FFWMobile        nvarchar(1000), 

        Email            nvarchar(1000), 

        ProfitCentre     nvarchar(1000), 

        Department       nvarchar(1000), 

        EliteNumber      nvarchar(1000), 

        Secretary        nvarchar(1000), 

        Location         nvarchar(1000), 

        Position         nvarchar(1000), 

        WorksFor1        nvarchar(1000), 

        WorksFor2        nvarchar(1000), 

        WorksFor3        nvarchar(1000), 

        WorksFor4        nvarchar(1000), 

        WorksFor5        nvarchar(1000), 

        WorksFor         nvarchar(1000), 

        AboutMe          nvarchar(1000), 

        Responsibilites  nvarchar(1000)

)

AS

      BEGIN  

       INSERT INTO @res

            SELECT  DISTINCT

                  EmployeeID      =       e.EmployeeID,

                  Status          =       es.EmployeeStatus,

                  Start_Date      =       e.EmployeeStartDate,

                  Emp_Name        =       e.Surname + ', ' + e.KnownAs,

                  LoginID         =       e.WindowsUserName,

                  UserID          =       e.DisplayEmployeeID,

                  Extension       =       e.ExtNoUD,

                  FFWMobile       =       e.FFWMobileUD,

                  Email           =       e.WorkEmail,

                  ProfitCentre    =       ch.HierarchyLevel3,

                  Department      =       ch.HierarchyLevel4,

                  EliteNumber     =       ejwrks.Elitenumberud,

                  Secretary       =       sec.EmployeeDescNoID,

                  Location        =       e.LocationUD,

                  Position        =       ej1.PostID,

                  WorksFor1       =       EWF1.EmployeeDescNoID,

                  WorksFor2       =       EWF2.EmployeeDescNoID,

                  WorksFor3       =       EWF3.EmployeeDescNoID,

                  WorksFor4       =       EWF4.EmployeeDescNoID,

                  WorksFor5       =       EWF5.EmployeeDescNoID,

                  WorksFor        =       EBoss.EmployeeDescNoID,

                  AboutMe         =       cast(e.AboutMeud as varchar(50)),

                  Responsibilites =       cast(e.Responsibilitiesud as varchar(50))

 

             

            FROM    [Cascade].dbo.Employee          e

                  JOIN    [Cascade].dbo.Employee_Status   es  ON      e.EmployeeID    =       es.EmployeeID

                  LEFT OUTER JOIN 

                        (

                              select  t1.JobTitle, t1.EmployeeID, t1.PostID

                              from    [Cascade].dbo.EmployeeJobs      t1

                              join    (

                                          select  employeeId, min(Sequence) as sequence

                                          from    [Cascade].dbo.EmployeeJobs

                                          group by employeeID

                                    )                               t2 

                              on      t1.employeeid   =       t2.employeeID   

                              and     t1.Sequence     =       t2.Sequence

                        )                               ej1  

                  ON                      e.EmployeeID            =       ej1.EmployeeID

                  LEFT OUTER JOIN [Cascade].dbo.Employee sec ON sec.EmployeeID  =       e.SecretaryNameUD 

                  JOIN    [Cascade].dbo.EmployeeJobs ejwrks ON e.employeeid    =       ejwrks.employeeid

                  JOIN      [Cascade].dbo.CompanyHierarchy   ch ON ejwrks.HierarchyNodeUD  =   ch.HierarchyNode 

                  JOIN    [Cascade].dbo.Employee eBoss ON EBoss.EmployeeID = ejwrks.WorksForEmployeeID

                  LEFT OUTER JOIN  [Cascade].dbo.Employee EWF1 ON EWF1.EmployeeID  = e.Manager1ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF2 ON EWF2.EmployeeID = e.Manager2ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF3 ON EWF3.EmployeeID = e.Manager3ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF4 ON EWF4.EmployeeID = e.Manager4ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF5 ON EWF5.EmployeeID = e.Manager5ud

            WHERE   

                  es.EmployeeStatus = 'Current'  

                  AND ch.[InUse?]      = 'Live'

 

 

            UNION

            

            SELECT  DISTINCT

                  EmployeeID      =       NULL,

                  Status          =       tbl.Status,

                  Start_Date      =       NULL,

                  Emp_Name        =       tbl.Name,

                  LoginID         =       NULL,

                  UserID          =       tbl.UserID,

                  Extension       =       tbl.Extension,

                  FFWMobile       =       NULL,

                  Email           =       NULL,

                  ProfitCentre    =       NULL,

                  Department      =       tbl.Department,

                  EliteNumber     =       NULL,

                  Secretary       =       NULL,

                  Location        =       tbl.Location,

                  Position        =       NULL,

                  WorksFor1       =       NULL,

                  WorksFor2       =       NULL,

                  WorksFor3       =       NULL,

                  WorksFor4       =       NULL,

                  WorksFor5       =       NULL,

                  WorksFor        =       NULL,

                  AboutMe         =       NULL,

                  Responsibilites =       NULL

 

                    

             

            FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl

             

            WHERE    tbl.status = 5 OR tbl.status = 6

 

            RETURN

            

      END

Open in new window

0
 

Author Comment

by:mooriginal
Comment Utility
but when i try test run with
select * from [dbo].[udf_GetResources]
of course doesnt work as needs a parameter

Im assuming it would work cause the sproc would result back a term to search for... though not sure...

0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
How about...


Select * from [dbo].[udf_GetResources]()  <---empty brackets


As for the version of the function you created:
CREATE FUNCTION dbo.udf_GetResources (@table_variable varchar)
RETURNS @res TABLE
(
        EmployeeID       nvarchar(1000),  
.
.
.

This is a function that expects a thing called @table_variable that is a varchar when the function is called.

0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
I think you can just omit (@table_variable varchar) from your function, and call it like:

select * from [dbo].[udf_GetResources]()

and you will be good to go.
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
To be more clear:  

CREATE FUNCTION dbo.udf_GetResources ()
RETURNS @res TABLE
(
        EmployeeID       nvarchar(1000),  
.
.
.


then call it like:

select * from [dbo].[udf_GetResources]()

0
 

Author Comment

by:mooriginal
Comment Utility
ooh thanks dude
so close i feel like i owe you more points

it compile now like below

when I run
select * from [dbo].[udf_GetResources]()
get:

An insufficient number of arguments were supplied for the procedure or function dbo.udf_GetResources.

I can see why your using () as the Sproc for the search would in theory bring back something for the function too look up...




CREATE FUNCTION dbo.udf_GetResourcesMA1 ()

RETURNS @res TABLE

(

        EmployeeID       nvarchar(1000),   

        Status           nvarchar(1000), 

        Start_Date       nvarchar(1000), 

        Emp_Name         nvarchar(1000), 

        LoginID          nvarchar(1000), 

        UserID           nvarchar(1000), 

        Extension        nvarchar(1000), 

        FFWMobile        nvarchar(1000), 

        Email            nvarchar(1000), 

        ProfitCentre     nvarchar(1000), 

        Department       nvarchar(1000), 

        EliteNumber      nvarchar(1000), 

        Secretary        nvarchar(1000), 

        Location         nvarchar(1000), 

        Position         nvarchar(1000), 

        WorksFor1        nvarchar(1000), 

        WorksFor2        nvarchar(1000), 

        WorksFor3        nvarchar(1000), 

        WorksFor4        nvarchar(1000), 

        WorksFor5        nvarchar(1000), 

        WorksFor         nvarchar(1000), 

        AboutMe          nvarchar(1000), 

        Responsibilites  nvarchar(1000)

)

AS

      BEGIN  

       INSERT INTO @res

            SELECT  DISTINCT

                  EmployeeID      =       e.EmployeeID,

                  Status          =       es.EmployeeStatus,

                  Start_Date      =       e.EmployeeStartDate,

                  Emp_Name        =       e.Surname + ', ' + e.KnownAs,

                  LoginID         =       e.WindowsUserName,

                  UserID          =       e.DisplayEmployeeID,

                  Extension       =       e.ExtNoUD,

                  FFWMobile       =       e.FFWMobileUD,

                  Email           =       e.WorkEmail,

                  ProfitCentre    =       ch.HierarchyLevel3,

                  Department      =       ch.HierarchyLevel4,

                  EliteNumber     =       ejwrks.Elitenumberud,

                  Secretary       =       sec.EmployeeDescNoID,

                  Location        =       e.LocationUD,

                  Position        =       ej1.PostID,

                  WorksFor1       =       EWF1.EmployeeDescNoID,

                  WorksFor2       =       EWF2.EmployeeDescNoID,

                  WorksFor3       =       EWF3.EmployeeDescNoID,

                  WorksFor4       =       EWF4.EmployeeDescNoID,

                  WorksFor5       =       EWF5.EmployeeDescNoID,

                  WorksFor        =       EBoss.EmployeeDescNoID,

                  AboutMe         =       cast(e.AboutMeud as varchar(50)),

                  Responsibilites =       cast(e.Responsibilitiesud as varchar(50))

 

             

            FROM    [Cascade].dbo.Employee          e

                  JOIN    [Cascade].dbo.Employee_Status   es  ON      e.EmployeeID    =       es.EmployeeID

                  LEFT OUTER JOIN 

                        (

                              select  t1.JobTitle, t1.EmployeeID, t1.PostID

                              from    [Cascade].dbo.EmployeeJobs      t1

                              join    (

                                          select  employeeId, min(Sequence) as sequence

                                          from    [Cascade].dbo.EmployeeJobs

                                          group by employeeID

                                    )                               t2 

                              on      t1.employeeid   =       t2.employeeID   

                              and     t1.Sequence     =       t2.Sequence

                        )                               ej1  

                  ON                      e.EmployeeID            =       ej1.EmployeeID

                  LEFT OUTER JOIN [Cascade].dbo.Employee sec ON sec.EmployeeID  =       e.SecretaryNameUD 

                  JOIN    [Cascade].dbo.EmployeeJobs ejwrks ON e.employeeid    =       ejwrks.employeeid

                  JOIN      [Cascade].dbo.CompanyHierarchy   ch ON ejwrks.HierarchyNodeUD  =   ch.HierarchyNode 

                  JOIN    [Cascade].dbo.Employee eBoss ON EBoss.EmployeeID = ejwrks.WorksForEmployeeID

                  LEFT OUTER JOIN  [Cascade].dbo.Employee EWF1 ON EWF1.EmployeeID  = e.Manager1ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF2 ON EWF2.EmployeeID = e.Manager2ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF3 ON EWF3.EmployeeID = e.Manager3ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF4 ON EWF4.EmployeeID = e.Manager4ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF5 ON EWF5.EmployeeID = e.Manager5ud

            WHERE   

                  es.EmployeeStatus = 'Current'  

                  AND ch.[InUse?]      = 'Live'

 

 

            UNION

            

            SELECT  DISTINCT

                  EmployeeID      =       NULL,

                  Status          =       tbl.Status,

                  Start_Date      =       NULL,

                  Emp_Name        =       tbl.Name,

                  LoginID         =       NULL,

                  UserID          =       tbl.UserID,

                  Extension       =       tbl.Extension,

                  FFWMobile       =       NULL,

                  Email           =       NULL,

                  ProfitCentre    =       NULL,

                  Department      =       tbl.Department,

                  EliteNumber     =       NULL,

                  Secretary       =       NULL,

                  Location        =       tbl.Location,

                  Position        =       NULL,

                  WorksFor1       =       NULL,

                  WorksFor2       =       NULL,

                  WorksFor3       =       NULL,

                  WorksFor4       =       NULL,

                  WorksFor5       =       NULL,

                  WorksFor        =       NULL,

                  AboutMe         =       NULL,

                  Responsibilites =       NULL

 

                    

             

            FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl

             

            WHERE    tbl.status = 5 OR tbl.status = 6

 

            RETURN

            

      END

Open in new window

0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility

An insufficient number of arguments were supplied for the procedure or function dbo.udf_GetResources.



Didn't you rename it to:

dbo.udf_GetResourcesMA1
0
 

Author Comment

by:mooriginal
Comment Utility
huge apologies its a bit hectic here at moment and failing to multi-task

should have posted this

Parameters were not supplied for the function 'dbo.udf_GetResourcesMA1'.
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
I'm guessing you are missing something small here.

I've created a Function called dbo.test() and called it with

select * from dbo.test()


and it works fine.  Make sure the version of the function you created in your most recent example:

CREATE FUNCTION dbo.udf_GetResourcesMA1 ()
RETURNS @res TABLE
(
        EmployeeID
.
.
.
.




is the one you are trying to test.  Make sure you are connected to the db where it lives.  Sorry if this seems to simple, but the function you have created is *correct* and the select method is correct.
0
 

Author Comment

by:mooriginal
Comment Utility
simple is good like me ...

right so
sa connection to database where this lives
newly created and new error message so you were right about me with not doing it right
but error message is now

Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "(user generated expression).Expr1059" from OLE DB provider "SQLNCLI" for linked server "NEMESIS_LINKED".

Now not sure whats happening because if I take the select its complaining about after the union and run just the sql there is executes fine..

Think i dont want to waste your time anymore ....

select * from [dbo].[udf_GetResources_TEST]()
 
 

CREATE FUNCTION dbo.udf_GetResources_TEST()

RETURNS @res TABLE

(

        EmployeeID       nvarchar(1000),   

        Status           nvarchar(1000), 

        Start_Date       nvarchar(1000), 

        Emp_Name         nvarchar(1000), 

        LoginID          nvarchar(1000), 

        UserID           nvarchar(1000), 

        Extension        nvarchar(1000), 

        FFWMobile        nvarchar(1000), 

        Email            nvarchar(1000), 

        ProfitCentre     nvarchar(1000), 

        Department       nvarchar(1000), 

        EliteNumber      nvarchar(1000), 

        Secretary        nvarchar(1000), 

        Location         nvarchar(1000), 

        Position         nvarchar(1000), 

        WorksFor1        nvarchar(1000), 

        WorksFor2        nvarchar(1000), 

        WorksFor3        nvarchar(1000), 

        WorksFor4        nvarchar(1000), 

        WorksFor5        nvarchar(1000), 

        WorksFor         nvarchar(1000), 

        AboutMe          nvarchar(1000), 

        Responsibilites  nvarchar(1000)

)

AS

      BEGIN  

       INSERT INTO @res

            SELECT  DISTINCT

                  EmployeeID      =       e.EmployeeID,

                  Status          =       es.EmployeeStatus,

                  Start_Date      =       e.EmployeeStartDate,

                  Emp_Name        =       e.Surname + ', ' + e.KnownAs,

                  LoginID         =       e.WindowsUserName,

                  UserID          =       e.DisplayEmployeeID,

                  Extension       =       e.ExtNoUD,

                  FFWMobile       =       e.FFWMobileUD,

                  Email           =       e.WorkEmail,

                  ProfitCentre    =       ch.HierarchyLevel3,

                  Department      =       ch.HierarchyLevel4,

                  EliteNumber     =       ejwrks.Elitenumberud,

                  Secretary       =       sec.EmployeeDescNoID,

                  Location        =       e.LocationUD,

                  Position        =       ej1.PostID,

                  WorksFor1       =       EWF1.EmployeeDescNoID,

                  WorksFor2       =       EWF2.EmployeeDescNoID,

                  WorksFor3       =       EWF3.EmployeeDescNoID,

                  WorksFor4       =       EWF4.EmployeeDescNoID,

                  WorksFor5       =       EWF5.EmployeeDescNoID,

                  WorksFor        =       EBoss.EmployeeDescNoID,

                  AboutMe         =       cast(e.AboutMeud as varchar(50)),

                  Responsibilites =       cast(e.Responsibilitiesud as varchar(50))

 

             

            FROM    [Cascade].dbo.Employee          e

                  JOIN    [Cascade].dbo.Employee_Status   es  ON      e.EmployeeID    =       es.EmployeeID

                  LEFT OUTER JOIN 

                        (

                              select  t1.JobTitle, t1.EmployeeID, t1.PostID

                              from    [Cascade].dbo.EmployeeJobs      t1

                              join    (

                                          select  employeeId, min(Sequence) as sequence

                                          from    [Cascade].dbo.EmployeeJobs

                                          group by employeeID

                                    )                               t2 

                              on      t1.employeeid   =       t2.employeeID   

                              and     t1.Sequence     =       t2.Sequence

                        )                               ej1  

                  ON                      e.EmployeeID            =       ej1.EmployeeID

                  LEFT OUTER JOIN [Cascade].dbo.Employee sec ON sec.EmployeeID  =       e.SecretaryNameUD 

                  JOIN    [Cascade].dbo.EmployeeJobs ejwrks ON e.employeeid    =       ejwrks.employeeid

                  JOIN      [Cascade].dbo.CompanyHierarchy   ch ON ejwrks.HierarchyNodeUD  =   ch.HierarchyNode 

                  JOIN    [Cascade].dbo.Employee eBoss ON EBoss.EmployeeID = ejwrks.WorksForEmployeeID

                  LEFT OUTER JOIN  [Cascade].dbo.Employee EWF1 ON EWF1.EmployeeID  = e.Manager1ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF2 ON EWF2.EmployeeID = e.Manager2ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF3 ON EWF3.EmployeeID = e.Manager3ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF4 ON EWF4.EmployeeID = e.Manager4ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF5 ON EWF5.EmployeeID = e.Manager5ud

            WHERE   

                  es.EmployeeStatus = 'Current'  

                  AND ch.[InUse?]      = 'Live'

 

 

            UNION

            

            SELECT  DISTINCT

                  EmployeeID      =       NULL,

                  Status          =       tbl.Status,

                  Start_Date      =       NULL,

                  Emp_Name        =       tbl.Name,

                  LoginID         =       NULL,

                  UserID          =       tbl.UserID,

                  Extension       =       tbl.Extension,

                  FFWMobile       =       NULL,

                  Email           =       NULL,

                  ProfitCentre    =       NULL,

                  Department      =       tbl.Department,

                  EliteNumber     =       NULL,

                  Secretary       =       NULL,

                  Location        =       tbl.Location,

                  Position        =       NULL,

                  WorksFor1       =       NULL,

                  WorksFor2       =       NULL,

                  WorksFor3       =       NULL,

                  WorksFor4       =       NULL,

                  WorksFor5       =       NULL,

                  WorksFor        =       NULL,

                  AboutMe         =       NULL,

                  Responsibilites =       NULL

 

                    

             

            FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl

             

            WHERE    tbl.status = 5 OR tbl.status = 6

 

            RETURN

            

      END

Open in new window

0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
what if you just try to execute that second select by itself?
0
 

Author Comment

by:mooriginal
Comment Utility
yup thats what i mean
if i execute the 2nd select from same window
works no problems at all ...
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
You have made all your function table values nvarchar(1000).  Are all the values in that link server table nvarchar?
0
 

Author Comment

by:mooriginal
Comment Utility
no they arent

so your suggesting that i make them more relevant...
as i thought the didnt need to be ..

so each should match the select its calling ?
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
well, you can convert them in your selecct like:

           SELECT  DISTINCT
                  EmployeeID      =       NULL,
                  Status          =       tbl.Status,
                  Start_Date      =       NULL,
                  Emp_Name        =       convert(nvarchar(1000),tbl.Name),
                  LoginID         =       NULL,
                  UserID          =       tbl.UserID,
                  Extension       =      convert(nvarchar(1000), tbl.Extension),
                  FFWMobile       =       NULL,
                  Email           =       NULL,
                  ProfitCentre    =       NULL,
                  Department      =      convert(nvarchar(1000), tbl.Department),
                  EliteNumber     =       NULL,
                  Secretary       =       NULL,
                  Location        =      convert(nvarchar(1000), tbl.Location),
                  Position        =       NULL,
                  WorksFor1       =       NULL,
                  WorksFor2       =       NULL,
                  WorksFor3       =       NULL,
                  WorksFor4       =       NULL,
                  WorksFor5       =       NULL,
                  WorksFor        =       NULL,
                  AboutMe         =       NULL,
                  Responsibilites =       NULL
0
 

Author Comment

by:mooriginal
Comment Utility
oh i see

but then for example
(
UserID nvarchar(50),
Extension varchar(50),

etc

match them ?
0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
You are essentially filling up a table in that function.  The table has a definition at the top that includes the name and variable type of each column.  When you run your selects within that function, the type of item you are selected (and therefore inserting into the result table) needs to match the table column type.
0
 

Author Comment

by:mooriginal
Comment Utility
cool
that makes sense i shall do this om monday and let you know
thanks for continuing help...

so close .
0
 

Author Comment

by:mooriginal
Comment Utility
Skrile
thanks for all your help..

remapped all the columns - did a cast on the union table which was causing a small problem ran a select all worked fine !
result !



 

CREATE FUNCTION dbo.udf_GetResources_TEST_MA1()

RETURNS @res TABLE

(

        Status           nvarchar(50), 

        Start_Date       datetime, 

        Emp_Name         nvarchar(50), 

        LoginID          nvarchar(50), 

        UserID           nvarchar(50), 

        Extension        nvarchar(500), 

        FFWMobile        nvarchar(500), 

        Email            nvarchar(60), 

        ProfitCentre     nvarchar(1000), 

        Department       nvarchar(1000), 

        EliteNumber      nvarchar(50), 

        Secretary        nvarchar(101), -- Computed column 

        Location         nvarchar(500), 

        Position         nvarchar(50), 

        WorksFor1        nvarchar(101), 

        WorksFor2        nvarchar(101), 

        WorksFor3        nvarchar(101), 

        WorksFor4        nvarchar(101), 

        WorksFor5        nvarchar(101), 

        WorksFor         nvarchar(100), 

        AboutMe          varchar(50), 

        Responsibilites  varchar(50)

)

AS

      BEGIN  

       INSERT INTO @res

            SELECT  DISTINCT

                  Status          =       es.EmployeeStatus,

                  Start_Date      =       e.EmployeeStartDate,

                  Emp_Name        =       e.Surname + ', ' + e.KnownAs,

                  LoginID         =       e.WindowsUserName,

                  UserID          =       e.DisplayEmployeeID,

                  Extension       =       e.ExtNoUD,

                  FFWMobile       =       e.FFWMobileUD,

                  Email           =       e.WorkEmail,

                  ProfitCentre    =       ch.HierarchyLevel3,

                  Department      =       ch.HierarchyLevel4,

                  EliteNumber     =       ejwrks.Elitenumberud,

                  Secretary       =       sec.EmployeeDescNoID,

                  Location        =       e.LocationUD,

                  Position        =       ej1.PostID,

                  WorksFor1       =       EWF1.EmployeeDescNoID,

                  WorksFor2       =       EWF2.EmployeeDescNoID,

                  WorksFor3       =       EWF3.EmployeeDescNoID,

                  WorksFor4       =       EWF4.EmployeeDescNoID,

                  WorksFor5       =       EWF5.EmployeeDescNoID,

                  WorksFor        =       EBoss.EmployeeDescNoID,

                  AboutMe         =       cast(e.AboutMeud as varchar(50)),

                  Responsibilites =       cast(e.Responsibilitiesud as varchar(50))

 

             

            FROM    [Cascade].dbo.Employee          e

                  JOIN    [Cascade].dbo.Employee_Status   es  ON      e.EmployeeID    =       es.EmployeeID

                  LEFT OUTER JOIN 

                        (

                              select  t1.JobTitle, t1.EmployeeID, t1.PostID

                              from    [Cascade].dbo.EmployeeJobs      t1

                              join    (

                                          select  employeeId, min(Sequence) as sequence

                                          from    [Cascade].dbo.EmployeeJobs

                                          group by employeeID

                                    )                               t2 

                              on      t1.employeeid   =       t2.employeeID   

                              and     t1.Sequence     =       t2.Sequence

                        )                               ej1  

                  ON                      e.EmployeeID            =       ej1.EmployeeID

                  LEFT OUTER JOIN [Cascade].dbo.Employee sec ON sec.EmployeeID  =       e.SecretaryNameUD 

                  JOIN    [Cascade].dbo.EmployeeJobs ejwrks ON e.employeeid    =       ejwrks.employeeid

                  JOIN      [Cascade].dbo.CompanyHierarchy   ch ON ejwrks.HierarchyNodeUD  =   ch.HierarchyNode 

                  JOIN    [Cascade].dbo.Employee eBoss ON EBoss.EmployeeID = ejwrks.WorksForEmployeeID

                  LEFT OUTER JOIN  [Cascade].dbo.Employee EWF1 ON EWF1.EmployeeID  = e.Manager1ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF2 ON EWF2.EmployeeID = e.Manager2ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF3 ON EWF3.EmployeeID = e.Manager3ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF4 ON EWF4.EmployeeID = e.Manager4ud

                  LEFT OUTER JOIN [Cascade].dbo.Employee EWF5 ON EWF5.EmployeeID = e.Manager5ud

            WHERE   

                  es.EmployeeStatus = 'Current'  

                  AND ch.[InUse?]      = 'Live'

 

 

            UNION

            

            SELECT  DISTINCT

                 

                  Status          =       cast(tbl.Status as nvarchar(10)),

                  Start_Date      =       NULL,

                  Emp_Name        =       tbl.Name,

                  LoginID         =       NULL,

                  UserID          =       tbl.UserID,

                  Extension       =       tbl.Extension,

                  FFWMobile       =       NULL,

                  Email           =       NULL,

                  ProfitCentre    =       NULL,

                  Department      =       tbl.Department,

                  EliteNumber     =       NULL,

                  Secretary       =       NULL,

                  Location        =       tbl.Location,

                  Position        =       NULL,

                  WorksFor1       =       NULL,

                  WorksFor2       =       NULL,

                  WorksFor3       =       NULL,

                  WorksFor4       =       NULL,

                  WorksFor5       =       NULL,

                  WorksFor        =       NULL,

                  AboutMe         =       NULL,

                  Responsibilites =       NULL

 

                    

             

            FROM     NEMESIS_LINKED.Intranet.dbo.tblUsers tbl

             

            WHERE    tbl.status = 5 OR tbl.status = 6

 

            RETURN

            

      END

Open in new window

0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
Phew!  Outstanding.

Now that that's done, the real power of Table Functions is passing in parameters to filter the "sub tables" to your liking.
0
 

Author Comment

by:mooriginal
Comment Utility
haha im back again one last time hopefully...

when i try and create the sproc to call the tbf... i  get
Parameters were not supplied for the function 'DBA.dbo.udf_GetResources'
CREATE PROCEDURE [dbo].[sp_StaffSearch]

        (@strCriteria varchar(100))

AS
 

	SELECT

	

	CASE

	   WHEN vs.Status  = 'Current' THEN vs.Emp_Name

	   ELSE vs.Emp_Name + ' [Out of Office]'

	End As Emp_Name,

	vs.Status,

	vs.Start_Date,

	vs.LoginID,      

	vs.UserID,      

	vs.Extension,  

	vs.FFWMobile,

	vs.Email,      

	vs.ProfitCentre,

	vs.Department,    

	vs.EliteNumber,  

	vs.Secretary,    

	vs.Location,    

	vs.Position,    

	vs.WorksFor1,

	vs.WorksFor2,

	vs.WorksFor3,

	vs.WorksFor4, 

	vs.WorksFor5,

	vs.AboutMe,

	vs.Responsibilites
 

	FROM DBA.dbo.udf_GetResources vs
 

	WHERE vs.Extension is not null -- AND vs.Status = 'Current'

	  AND ((vs.Start_Date is null) OR (vs.Start_Date <= GetDate() )

	      )  

	AND ((vs.Emp_Name LIKE      '%' + @strCriteria + '%')

	  OR (vs.LoginID LIKE       '%' + @strCriteria + '%')

	  OR (vs.UserID     LIKE    '%' + @strCriteria + '%')

	  OR (vs.Extension   LIKE   '%' + @strCriteria + '%')

	  OR (vs.FFWMobile    LIKE  '%' + @strCriteria + '%')

	  OR (vs.Email    LIKE      '%' + @strCriteria + '%')

	  OR (vs.ProfitCentre LIKE  '%' + @strCriteria + '%')

	  OR (vs.Department LIKE    '%' + @strCriteria + '%')

	  OR (vs.EliteNumber LIKE   '%' + @strCriteria + '%')

	  OR (vs.Secretary LIKE     '%' + @strCriteria + '%')

	  OR (vs.Location  LIKE     '%' + @strCriteria + '%')

	  OR (vs.Position  LIKE     '%' + @strCriteria + '%')

	  OR (vs.WorksFor1  LIKE    '%' + @strCriteria + '%')

	  OR (vs.WorksFor2  LIKE    '%' + @strCriteria + '%')

	  OR (vs.WorksFor3  LIKE    '%' + @strCriteria + '%')

	  OR (vs.WorksFor4  LIKE    '%' + @strCriteria + '%')

	  OR (vs.WorksFor5  LIKE    '%' + @strCriteria + '%')

	  OR (vs.AboutMe  LIKE      '%' + @strCriteria + '%')

	  OR (vs.Responsibilites LIKE  '%' + @strCriteria + '%')

	    )

	 ORDER BY vs.Emp_Name ASC

Open in new window

0
 
LVL 16

Expert Comment

by:Steve Krile
Comment Utility
Yeah, you need the empty brackets after the udf, like this:

      vs.Responsibilites
 
      FROM DBA.dbo.udf_GetResources() vs    <-------------EMPTY BRACKETS
 
      WHERE vs.Extension is not null -- AND vs.Status = 'Current'
0
 

Author Comment

by:mooriginal
Comment Utility
dude can you post on this thread and get some more points
the guy answering me is not helping

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23853553.html

ive just tried it and it worked thanks !

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now