What is wrong with the code below

Question

CREATE FUNCTION GetPayCodeList
(     
     -- Add the parameters for the function here
      @PC varchar(50)
)

RETURNS TABLE 
AS
RETURN 
(     IF @PC = '*'
       SELECT DISTINCT ID, Code, Description
                            FROM   tbl
      ELSE
             SELECT DISTINCT ID, Code, Description
                            FROM   tbl
          WHERE Code = @PC
)

Answer 1

Are any of those columns character and DISTINCT?

As a side note, you could rewrite it as this to make it simpler:

SELECT DISTINCT ID, Code, Description
    FROM   tbl
    WHERE @PC = '*' OR Code = @PC

Answer 2

Try this:

CREATE FUNCTION GetPayCodeList(          -- Add the parameters for the function here      
@PC varchar(50))
RETURNS @tbl TABLE (
    ID int
    , Code varchar(50)
    , Description varchar(max))
WITH SCHEMABINDING  
AS
BEGIN
IF @PC = '*'      
    SELECT DISTINCT ID, Code, Description                            
    FROM   tbl      
ELSE             
    SELECT DISTINCT ID, Code, Description                            
    FROM   tbl          
    WHERE Code = @PC
END

Answer 3

You will have to write a multi-step function, you cannot do that an inline function.

CREATE FUNCTION GetPayCodeList
(     
     -- Add the parameters for the function here
      @PC varchar(50)
)

RETURNS @table TABLE (ID int NOT NULL,
... //others fields ) AS
BEGIN
IF @PC = '*'
INSERT @table (SELECT DISTINCT ID, Code, Description
                        FROM   tbl) FROM tbl
ELSE
INSERT @table ( SELECT DISTINCT ID, Code, Description
                        FROM   tbl
      WHERE Code = @PC) FROM tbl
RETURN @table
END