0

The query concerns one table holding Contacts data. A table valued function is used to return the "status" for each Contact.

The insert statement grabs the unique key from the Contacts table and loads it into a variable called @filteredIdList. This is then joined to the Contacts table and again to the table-valued function by passing the variable @filteredIdList into it.

I have simplified the example down to show how I'm currently returning the status. This needs to be changed into a view instead:

DECLARE @filteredIdList IdList;
INSERT INTO @filteredIdList SELECT ContactID FROM Contacts

SELECT 
       Contacts.ContactID, 
       Contact_Status

FROM   Contacts
       INNER JOIN @filteredIdList [IDLIST] ON Contacts.ContactID = [IDLIST].[id]
       INNER JOIN tvfContact_Status(@filteredIdList) Contact_Status
       ON [IDLIST].[id] = Contact_Status.ContactID

I have tried creating it as a CTE inside the view but couldn't figure it out.

user3491557
  • 15
  • 2

2 Answers2

0

Not sure why you need a view here, but if you want to do this try the below way

Since the tvfContact_Status is used to return the status of each contact. Change the Table valued function to Scalar function.

First alter the tvfContact_Status function

ALTER FUNCTION dbo.tvfContact_Status(@ContactID int)
RETURNS varchar(50) --Status
AS 
BEGIN
    --------
    RETURN @Status;
END;
GO

Create a View like this

CREATE VIEW Contact_View
AS
SELECT 
       Contacts.ContactID, 
       dbo.tvfContact_Status(ContactID) as Contact_Status
FROM   Contacts

Note: This is not a efficient way to do this

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Hi VR46, thanks for the response. These used to be scalar functions that were changed by the vendor to table valued due to performance issues (and they were CLR's/.net runtimes). We have to find a method to handle them as table valued because there are thousands of them (long story). – user3491557 Jan 28 '16 at 01:26
  • @user3491557 - can you add the `tvfContact_Status` code – Pரதீப் Jan 28 '16 at 01:29
  • I have to do cleanup on the code before I can paste it. Won't have time today but will aim to have something tomorrow. Thank you – user3491557 Jan 28 '16 at 02:19
  • @user3491557 - No issues. After editing the question just comment here. Will look into to it – Pரதீப் Jan 28 '16 at 03:57
0

I believe the CTE you are looking for would be.

;with nameofcte (IdList) as (
  select ContactID from Contacts
)

then you would just use nameofcte in your join statement.

I do not use CTEs everyday so I normally give this post on Simple Talk a read to refresh myself on them before using them. https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

Mike Harris
  • 869
  • 9
  • 21
  • Hi Mike, therein lies the problem. I'm not sure how I'm supposed to pass the result from the CTE into the table valued function. So for example, this does not work: – user3491557 Jan 28 '16 at 01:44
  • WITH Contact_Status (Id) AS ( select distinct ContactID from Contacts ) SELECT C.ContactID, ContactStatus FROM Contacts C INNER JOIN Contact_Status on Contact_Status.Id = C.ContactID INNER JOIN dbo.tvfContact_Status(Contact_Status.Id) ContactStatus ON Contact_Status.Id = C.ContactID – user3491557 Jan 28 '16 at 01:44
  • It says: Msg 4104, Level 16, State 1, Line 28 The multi-part identifier "Contact_Status.Id" could not be bound. – user3491557 Jan 28 '16 at 01:45
  • Try a sub-select dbo.tvfContact_Status(select Id from Contact_Status) You may need a top 1 on the sub-select but from the sample it seems to only return one value. – Mike Harris Jan 28 '16 at 01:58
  • The initial query I gave returns thousands of rows, which it should and what I want the new query to do also. In regards to trying it with the sub select, the function doesn't seem to know what do with that: Msg 156, Level 15, State 1, Line 83 Incorrect syntax near the keyword 'select'. – user3491557 Jan 28 '16 at 02:24