Share via


how to use IF-THEN-ELSE in a inline table-valued function

Question

Saturday, December 12, 2009 8:25 PM

Hi

 

I define a inline table-valued function as following

 

ALTER FUNCTION [dbo].[GetElt](@Id int)

RETURNS TABLE

AS RETURN select name from dbo.Elt where Id=@Id

 

Is it possible to use if then else inside ?

Like

 

ALTER FUNCTION [dbo].[GetElt](@Id int)

RETURNS TABLE

AS RETURN

If @id=0

select name from dbo.Elt
else

select name from dbo.Elt where Id =@Id

 

thanks

Fred

 

All replies (4)

Saturday, December 12, 2009 8:41 PM ✅Answered | 1 vote

This will work for you:

...AS RETURN
SELECT Name
FROM dbo.Elt
WHERE @Id=0 or Id=@Id

--Brad (My Blog)


Saturday, December 12, 2009 8:47 PM ✅Answered | 1 vote

Inline TVF must be a single SELECT.

You can do regular TVF with IF..ELSE.....

Samples: http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com


Sunday, December 13, 2009 11:27 AM ✅Answered | 2 votes

No, that's not possible. For a multi-statement table-valued function, you need to specify explicitly what columns will be returned.

Also keep in mind that there is a huge performance difference between inline table-valued functions and multi-statement table-valued functions.
When an inline TVF is used, its definition is copied into the query and the result is sent to the optimizer. This might cause your query to get a plan where part of the TVF is intermingled with pat of the referencing query, if the optimizer decides that the results will bbe the same but performance will be better.
When a multi-statement TVF is used, it has to be called once (if used with fixed arguments) or once for each row (if used with APPLY), with the result being stored in an internal structure that is then used in the referencing query; also, since there are no statistics on the result of the TVF when the query is optimized, some standard numbers are used which may be off my a mile.

In short - multi-statement TVFs tend to be terrible for performance, while inline TVFs rarely have adverse performance effects. So only use a multi-statement TVF when there is no other alternative. In this case, there is a very easy alternative, as suggested by Brad.-- Hugo Kornelis, SQL Server MVP


Sunday, December 13, 2009 5:48 AM

Hi
Thanks a lot
is it possible to return all columns (I simplify the select...)

ALTER

 

FUNCTION [dbo].[GetGrid](@Id int)
RETURNS @Table TABLE**(*)** ????
BEGIN
IF @Id=0
INSERT @Table select * from dbo.Elt
ELSE
INSERT @Table select * from dbo.Elt where Id = @Id
RETURN