CODEWITHSUNDEEP
sql-server
Alexander Hristov
Alexander Hristov

Reputation: 311

Table-valued function displaying only 1 result

I've got this table valued function and I want it to display all records with the same "PeopleID". How can I do it? It's currently displaying only 1 of the possible results.

ALTER FUNCTION dbo.ufnGetContactInformation(@FuncID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    FuncID int PRIMARY KEY NOT NULL, 
    Full_Name nvarchar(75) NULL, 
    Phone nvarchar(100) NULL, 
    Email nvarchar(50) NULL, 
    City nvarchar(20) NULL
)
AS 
BEGIN
    DECLARE
        @fullname nvarchar(75),
        @phonenumber nvarchar(100),
        @email nvarchar(50),
        @city nvarchar(20);

    -- > Get common contact information
    SELECT
            @fullname = p.Full_Name,
            @phonenumber = c.Phone, 
            @email = c.Email,
            @city = c.CityID
            FROM d_People p, d_Contacts c
            WHERE p.PeopleID=@FuncID;
    -- < Get common contact information
    IF @FuncID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @FuncID, @fullname, @phonenumber, @email, @city;
    END;
    RETURN;
END;
GO

Upvotes: 1

Views: 408

Answers (1)

SqlZim
SqlZim

Reputation: 38063

You need a proper join for your two tables, otherwise your query makes no sense. What you have now will give you one person full_name, but every contact phone, email, and city.

Bad habits to kick : using old-style JOINs - Aaron Bertrand

When you select into the scalar variables, you will only get one result in each variable. Inserting the variables after setting them to only hold one row will only ever yield at most one row.

You can simplify your function as an in-line table-valued function like so:

alter function dbo.ufngetcontactinformation(@funcid int)
returns table as return (
  select 
     funcid = p.peopleid
   , fullname = p.full_name
   , phonenumber = c.phone
   , email = c.email
   , city = c.cityid
  from d_people p, d_contacts c
  where p.peopleid = @funcid;
);
go

Reference

If you have to have a multi-statement table valued function despite the perfomance hit:

alter function dbo.ufngetcontactinformation(@funcid int)
returns @retcontactinformation table 
(
    -- columns returned by the function
    funcid int primary key not null, 
    full_name nvarchar(75) null, 
    phone nvarchar(100) null, 
    email nvarchar(50) null, 
    city nvarchar(20) null
)
as 
begin
  insert @retcontactinformation
select 
    @funcid
  , p.full_name
  , c.phone
  , c.email
  , c.cityid
from d_people p, d_contacts c
where p.peopleid = @funcid;
return;
end;
go

Upvotes: 2

Related Questions