CODEWITHSUNDEEP
sql-serverfunctionuser-defined-functionsdeclaretable-functions
esquare
esquare

Reputation: 4265

Declare variable in table valued function

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

Upvotes: 139

Views: 228049

Answers (3)

Uddhav Dandale
Uddhav Dandale

Reputation: 1

This is one of the examples 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')

Upvotes: 0

Violet
Violet

Reputation: 361

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)

)

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 265

Related Questions