在表值函数中声明变量

Question

如何在表值函数中声明变量？

Answer 1

表值函数有两种风格。一种只是一条 select 语句，另一种可以包含比仅仅一条 select 语句更多的行。

这不能有变量：

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

你必须这样做：

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

Answer 2

在 SQL Server 中：

这不是一个非常好的解决方案，但是如果您有充分的理由需要使用内联 TVF 而不是 MSTVF，并且无法将变量作为参数传递到 TVF，但可以使用 SELECT 语句获取它，您可以使用 CTE 来访问该值，如下所示：

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)

)

Answer 3

这是我尝试过的例子之一。

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')