Functions returns empty set

Question

I have successfully created a function in SQL 2008:

ALTER FUNCTION [dbo].Func_raw_data_xref (@ColName AS NVARCHAR(255)) 
RETURNS @VVSrcCDs TABLE ( 
  VV_SRC_CD      NVARCHAR(255) NULL, 
  VV_CD          NVARCHAR(255) NULL, 
  VV_SRC_CD_DESC NVARCHAR(255) NULL, 
  VV_DSC         NVARCHAR(255) NULL ) 
AS 
  BEGIN 
      DECLARE @vv_SRC_CD      NVARCHAR(255), 
              @vv_CD          NVARCHAR(255), 
              @vv_SRC_CD_DESC NVARCHAR(255), 
              @vv_DSC         NVARCHAR(255); 

      SELECT @vv_SRC_CD = A.VV_SRC_CD, 
             @vv_CD = A.VV_CD, 
             @vv_SRC_CD_DESC = A.VV_SRC_CD_DESC, 
             @vv_DSC = A.VV_DSC 
      FROM   DBO.VALUES A 
             JOIN VVLOOKUP B 
               ON A.VV_SRC_CD = b.VV_SRC_CD 
      WHERE  B.[CLIENT COLUMN NAME] = @ColName 

      RETURN; 
  END 

The problem is when I call the function:

SELECT * 
FROM   DBO.Func_raw_data_xref('_CD'); 

I get no results. What I do get are the columns referenced in the function but no data. If I copy the select statement out of the function and run it with a valid parameter for @ColName, I do get results.

Answer 1

You don't have to assign result of your select to a local variables. What you have to do is INSERT results of that SELECT into table variable you declared as return value:

Instead of

  DECLARE 
     @vv_SRC_CD NVARCHAR(255),
     @vv_CD NVARCHAR(255),
     @vv_SRC_CD_DESC NVARCHAR(255),
     @vv_DSC NVARCHAR(255);

    SELECT 
        @vv_SRC_CD = A.vv_SRC_CD,
        @vv_CD = A.vv_CD, 
        @vv_SRC_CD_DESC = A.vv_SRC_CD_DESC,
        @vv_DSC = A.vv_DSC
    FROM DBO.values A
    JOIN VVLookup B
    ON A.vv_SRC_CD = b.vv_SRC_CD
    WHERE B.[Client Column Name] = @ColName

Try

INSERT INTO @VVSrcCDs
SELECT 
            A.vv_SRC_CD,
            A.vv_CD, 
            A.vv_SRC_CD_DESC,
            A.vv_DSC
        FROM DBO.values A
        JOIN VVLookup B
        ON A.vv_SRC_CD = b.vv_SRC_CD
        WHERE B.[Client Column Name] = @ColName