Table variable in User Defined Function SQL Server and performance of UDF

Question

I have an UDF like this

CREATE FUNCTION Lob_function
(
    @policy NVARCHAR(MAX)
    @table  Table
)
RETURNS NVARCHAR(MAX)
AS 
BEGIN
select @policy= 
case
when @policy like '%AMM%' then 'AMM'
when @policy like '%MOT%' then 'MOT'
when @policy like '%MOX%' then 'MOX'
when @policy not like '00%' then LEFT(@policy,3)
end
from @table
return @policy
END;

I want to use my UDF for various cases like :

Select Lob_function (@policy, @table) from @table.

It appears an error on @table Table, when I replace @table by a fixed table, my UDF can be executed but very slowly compared with a normal Select statement.

Possible duplicate of [Table name as variable](https://stackoverflow.com/questions/2838490/table-name-as-variable) — GSerg, Jan 08 '20 at 08:57
What is the datatype `table`? It can't be a User Defined Type, as `TABLE` is a reserved keyword (so it it were, you would have to declare it as `@table dbo.[Table]`. I assume that `@table` is a User Defined table type, so what's the error you're getting? You don't actually tell me. What is your goal here? — Thom A, Jan 08 '20 at 09:11

score 2 · Accepted Answer · answered Jan 08 '20 at 12:28

You can't really do what you want.

First, you cannot pass tables as parameters into functions. As the documentation explains:

Parameters can take the place only of constants; they cannot be used instead of table names, column names, or the names of other database objects.

In theory, you could use dynamic SQL to construct a query and run it. Unfortunately, the only use of execute is for extended functions. This is a little buried in the documentation, but it is there:

User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.

That really doesn't leave any good options. You might try to think of another approach that doesn't require storing related data in multiple different tables. Or perhaps you can use a stored procedure instead of a UDF.

There are some very arcane work-arounds, which involve using an extended stored procedure or CLR to execute a batch command which in turn passes a query into the database. That is really complicated, prone to error, and I've never actually seen it used in production code.

Thank you for your clear answer. I will use stored procedure instead. — Dinh Quang Tuan, Jan 08 '20 at 13:40

Table variable in User Defined Function SQL Server and performance of UDF

1 Answers1