CODEWITHSUNDEEP
sql-server-2008t-sql
Leron
Leron

Reputation: 9886

Table Valued Function is returning only one row when the actual result contains multiple rows

I have table Clients that contains many columns, however I'm targeting two particular columns ClientId and PIN. The example that I'm going to provide is not making much sense but I'm just learning more advanced SQL (T-SQL) topics and my final goal is something a lot different than what is here.

So I made a function which I would like to return all rows that match certain search criteria. So I have this:

CREATE FUNCTION ufn_NumberOfClients (@ClientId INT)
  RETURNS @retContactInformation TABLE 
  (
    -- Columns returned by the function
    ClId int PRIMARY KEY NOT NULL,
    PIN nvarchar(50) NULL
  )
  AS
  BEGIN
    DECLARE 
    @ClId INT, 
    @PIN nvarchar(50) 

    SELECT @ClId = ClientId, @PIN = PIN
    FROM Client
    WHERE PIN LIKE '%7788%'

    IF @ClientId IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ClId, @PIN;
    END;
RETURN;
END

But when I execute the function:

SELECT * FROM
ufn_NumberOfClients(4)

I get only one result. Even though it's a valid result, if I execute just 

SELECT * FROM Client
WHERE PIN LIKE '%7788%'

I get 5 results, and the function is returning just the last one.

Two thing which worth mentioning. The example which I use to write the function the ClId int PRIMARY KEY NOT NULL, from the table is not DECLAREed after that, but I got error if I don't do it. Also, I pass variable, but I think it's not used so it shouldn't affect the behaviour (or at least I think so).

So how can I return all the results matching the search crieria with a function?

Upvotes: 2

Views: 1506

Answers (1)

MaxiWheat
MaxiWheat

Reputation: 6261

You actually insert only one row in the table variable right now, Try this instead :

CREATE FUNCTION ufn_NumberOfClients (@ClientId INT)
RETURNS @retContactInformation TABLE 
(
-- Columns returned by the function
ClId int PRIMARY KEY NOT NULL,
PIN nvarchar(50) NULL
)
AS
BEGIN

INSERT INTO @retContactInformation
SELECT ClientId, PIN
FROM Client
WHERE PIN LIKE '%7788%';

RETURN;
END

Upvotes: 3

Related Questions