Declare variable in table valued function

Question

How can I declare a variable in a table valued function?

Rondeau

inline or multi-statement? Like MSDN describes them? – Roselynroseman

Answer 1

There are two flavors of table valued functions. One that is just a select statement and one that can have more rows than just a select statement.

This can not have a variable:

create function Func() returns table
as
return
select 10 as ColName

You have to do like this instead:

create function Func()
returns @T table(ColName int)
as
begin
  declare @Var int
  set @Var = 10
  insert into @T(ColName) values (@Var)
  return
end

Para

The first example is known as an "Inline Table-Valued Function" which has performance benefits compared to a Multi-statement Table-Valued Function, namely the database server can recompose the query with the ITVF inlined into the parent query, essentially becoming a parameterised VIEW whereas a MSTVF behaves more like an opaque stored-procedure (though with its own advantages compared to sprocs). Inline functions should be preferred over MSTVF. If you do need to calculate and store intermediate values (such as the result of a complex scalar function expression) then use a subquery. – Adaptive

It's probably also worth mentioning that if the outcome of whatever you are using to populate the variable you wish to set is in any way generalisable, then you could consider writing a separate function to generate it. This would allow you to use the ITVF described by @Adaptive above, with all the benefits thereof, while still inserting a dynamically generated value into your function. I just wrote a function with the help of the above solution (thank you @MikaelEriksson!) which passes on one of its parameters to a helper function to save me having to use the MSTVF form. – Glove

the biggest cost is inserting for my function and I don't know how to skip this cost without inserting to table variable and return result of select – Bluebottle

@Glove it would be great to see an example of this. Have you considered providing another answer along with your suggestion? – Argosy

Answer 2

In SQL Server:

It's not a very nice solution, but if you have a valid reason for needing to use an inline TVF instead of a MSTVF and cannot pass the variable into the TVF as a parameter, but can obtain it using a SELECT statement, you can use a CTE to access the value as follows:

CREATE FUNCTION func()
RETURNS TABLE
AS 
RETURN
(
-- Create a CTE table containing just the required value
WITH cte_myVar AS
   (SELECT <statement to select variable>) col1

-- Use the value by selecting it from the CTE table
SELECT * FROM table1 t1
WHERE t1.col1 = (SELECT col1 FROM cte_myVar)

)

Custombuilt

Answer 3

This is one of the example I tried.



USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[TABLEFUNCTION1]
(   
 @quarterValue  VARCHAR(6)
)
RETURNS @T table (releaseYearText           VARCHAR(20),
                   releasePreviousYearText  VARCHAR(20),
                   quarterText              VARCHAR(20),
                   quarterID                VARCHAR(20))
AS
BEGIN
 DECLARE @year                     VARCHAR(MAX)= NULL,
              @quarter                  VARCHAR(MAX)= NULL,
              @releaseYearText          VARCHAR(MAX)= NULL,
              @releasePreviousYearText  VARCHAR(MAX)= NULL,
              @quarterText              VARCHAR(MAX)= NULL,
              @quarterID                VARCHAR(MAX)= NULL,
              @databaseName             VARCHAR(MAX)= NULL;
      SET @databaseName = 'CDL';
      SET @year =  SUBSTRING(@quarterValue, 1, 4);
      SET @quarter = CONCAT(' Quarter ', SUBSTRING(@quarterValue, 6, 6));

      SET @releasePreviousYearText =
      CASE
          WHEN CAST(SUBSTRING(@quarterValue, 6, 6) AS int) > 1 THEN CAST(@year AS int) -1
          ELSE @year
      END ;

      SET @releaseYearText         = CONCAT(CONCAT('Year ', SUBSTRING(@quarterValue, 1, 4)),@Quarter)
      SET @releasePreviousYearText = CONCAT('Year ', SUBSTRING(@quarterValue, 1, 4))
      SET @quarterText             = @quarterValue
      SET @quarterID               =  SUBSTRING(@quarterValue, 1, 4) ;

       INSERT INTO @T(releaseYearText,
                      releasePreviousYearText,
                      quarterText,
                      quarterID
       ) VALUES (     @releaseYearText,
                      @releasePreviousYearText,
                      @quarterText,
                      @quarterID
       )
       return 
  
END



--select * from  [dbo].[TABLEFUNCTION1]('2024Q1')

Abie