Limitation of RETURN + TABLE VALUED Function

Question

Limitation of RETURN + TABLE VALUED Function

karthik M

One Orange Chip

Points: 29139
More actions
March 22, 2010 at 3:17 am

#218428

Hi All,
I have a table valued function.
CREATE FUNCTION [dbo].[fnPointRanked]
(
@AssetIDINT
)
RETURNS @Ret TABLE
(
[ID] [int] NOT NULL ,
[AssetID] [int] NOT NULL ,
[AssetType_Code] [int] NOT NULL ,
[NAV] [float] NULL ,
[Return] [float] NULL ,
[Retrieve_Flag] [Char] NULL
)
AS
BEGIN
<function logic>
RETURN -- Here, if Retrieve_Flag is 'Y' then i will choose only those records. I will update this field inside the function. So this filed is not required in the final result. how should i eliminate it from the final result?
END
karthik

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Don't define something in a RETURN that you don't actually want to return. You need to omit that column.

This means that you will need to create a second work table that has that column so you can develop your flag. Then you could populate your return table with only those rows that have a value of "Y" in the retrieve flag column. Of course, using worktables at all means that your query performance will be slowed down compared to that of an inline table valued function.

Alternatively, you can develop the retrieve flag in a CTE and then omit it from the SELECT list in the query referencing that CTE.

create table #temp (ID int, Value varchar(20))

insert into #temp

select 1, 'Apple' union all

select 2, 'Banana' union all

select 3, 'Cantaloupe'

-- code to process sample data above

-- (obviously your criteria for the retrieve flag will be more complicated than a simple LIKE test)

;with cte1 as

(Select ID,Value,case when Value like 'B%' then 'Y' else '' end as retrieveFlag

from #temp)

select ID,Value -- leaving out retrieveFlag

from cte1

where retrieveFlag = 'Y'

drop table #temp

This seems childishly simple. Am I misunderstanding your question?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

karthik M One Orange Chip Points: 29139 More actions · Answer 2

You are correct. But i would like to know is it possible to exclude the column in the return statement itself. i.e wwithout using the # table.

karthik

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

Best thing for you to do is to just go one step ahead and test it to see if it works!

As per BOL,

No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.

http://msdn.microsoft.com/en-us/library/ms191165(SQL.90).aspx

On the contrary you have 'Inline table-valued functions' which can retrun the select statements , (from BOL)

Inline Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]

http://msdn.microsoft.com/en-us/library/ms186755(SQL.90).aspx

If you dont have the luxury of altering the function, I would suggest you to create a view on top of that function and return whatever you want.

---------------------------------------------------------------------------------

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Karthikeyan, I believe you are confusing the two different types of table-valued functions. The following quote is from BOL "Types of Functions". I've added emphasis in italics.

User-defined table-valued functions return a table data type. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement.

You are using a RETURN TABLE statement, which means that you are creating a user-defined table-valued function. It returns all columns you specified in the RETURN TABLE definition. So, no... you can't omit the retrieved_flag column.

By contrast, an inline table-valued function is a single query. It has no RETURN TABLE declaration, and it returns whatever columns are specified in the SELECT list for that query. It is important to keep the two types separate, because inline table-valued functions are much faster than user-defined table-valued functions. However, all work must be done within a single query. With inline table-valued functions you cannot use temp tables or variables to store values such as your retrieve flag.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills