如何在表值函数中声明变量?
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
在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)
)