How to avoid converting data in the nvarchar phone number column to int

Question

Hello everyone. I created an inline spreadsheet function that, based on the parameter entered, which is essentially a number of some found in BusinessEntityID, displays personal information about the employee. One of these data is the phone number. However when I call this function it shows me that it cannot convert the pp.PhoneNumber column from nvarchar to int. Can this conversion be bypassed because I need a nvarchar data type in the pp.PhoneNumber column

Here is my query
Create FUNCTION dbo.Workersprivatedata (@ID [int])
RETURNS TABLE
RETURN
(WITH CTE([WORKERID],[Name],[MiddleName],[LastName],[Email],[City],[PhoneNumber])
AS
(SELECT p.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName, ea.EmailAddress,a.City, pp.PhoneNumber FROM Person.Person p join Person.EmailAdDress ea on
p.BusinessEntityID=ea.BusinessEntityID join Person.BusinessEntity b on p.BusinessEntityID=b.BusinessEntityID join
Person.BusinessEntityAddress bea on b.BusinessEntityID=bea.BusinessEntityID
join Person.[Address] a on bea.AddressID=a.AddressID LEFT OUTER JOIN Person.PersonPhone pp on p.BusinessEntityID=pp.PhoneNumber
)
SELECT * FROM CTE
WHERE @ID=WORKERID
)
Select * from Workersprivatedata(5)

This is what it looks like

Answer 1

Hi @Sajo_Nez1510 ，

Welcome to the microsoft TSQL Q&A forum!

Please refer to:

Create FUNCTION dbo.Workersprivatedata (@ID [int])  
RETURNS TABLE  
RETURN  
(WITH CTE([WORKERID],[Name],[MiddleName],[LastName],[Email],[City],[PhoneNumber])  
AS  
(SELECT p.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName, ea.EmailAddress,a.City, pp.PhoneNumber FROM Person.Person p join Person.EmailAdDress ea on  
p.BusinessEntityID=ea.BusinessEntityID   
join Person.BusinessEntity b on p.BusinessEntityID=b.BusinessEntityID   
join Person.BusinessEntityAddress bea on b.BusinessEntityID=bea.BusinessEntityID  
join Person.[Address] a on bea.AddressID=a.AddressID   
LEFT OUTER JOIN Person.PersonPhone pp on cast(p.BusinessEntityID as nvarchar)=pp.PhoneNumber  
)  
SELECT FROM CTE  
WHERE @ID=WORKERID  
)  
Select * from Workersprivatedata(5)  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

So what did you have in mind here:

LEFT OUTER JOIN Person.PersonPhone pp on p.BusinessEntityID=pp.PhoneNumber

Are you really looking for people whose BusinessEntityID matches some other persons phone number?

Or could it be that you have grabbed hold of the wrong column?