CODEWITHSUNDEEP
sql-server-2005
rfmcmichael
rfmcmichael

Reputation:

What is wrong with the code below

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
)

Upvotes: 0

Views: 110

Answers (3)

RRUZ
RRUZ

Reputation: 136421

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

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294367

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

Upvotes: 0

lavinio
lavinio

Reputation: 24309

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

Upvotes: 2

Related Questions