Solved

Ideas on Cross search

Posted on 2008-10-20
47
312 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
ID: 22758076
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
ID: 22758090
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
ID: 22758425
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 16

Expert Comment

by:Steve Krile
ID: 22758518
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
ID: 22758667
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
ID: 22758744
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
ID: 22759442
yup good point ill pick this up tomorrow and look at logic too..
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22759876
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
ID: 22765761
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
ID: 22765950
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
ID: 22766808
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
ID: 22767137
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
ID: 22768051
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
ID: 22794627
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
ID: 22794680
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
ID: 22794926
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
ID: 22794976
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
ID: 22794979
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
ID: 22795124
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
ID: 22795163
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
ID: 22795904
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
ID: 22795991
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
ID: 22796021
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
 

Author Comment

by:mooriginal
ID: 22796274
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
ID: 22796295
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
ID: 22796334
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
ID: 22796509
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
ID: 22796531
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
ID: 22796552
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
ID: 22796990
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
ID: 22797017

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
ID: 22797158
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
ID: 22797225
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
ID: 22797468
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
ID: 22797577
what if you just try to execute that second select by itself?
0
 

Author Comment

by:mooriginal
ID: 22797704
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
ID: 22797735
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
ID: 22797805
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
ID: 22797821
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
ID: 22797965
oh i see

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

etc

match them ?
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22797980
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
ID: 22802031
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
ID: 22811404
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
ID: 22811452
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
ID: 22820240
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
ID: 22820464
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
ID: 22820514
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server concatenate fields 10 36
Whats wrong in this query - Select * from tableA,tableA 11 32
Can > be used for a Text field 6 51
SQL Count issue 24 17
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

832 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