SQL 中表返回函数使用 if 语句的问题

发布于 2024-08-15 04:22:59 字数 393 浏览 10 评论 0原文

我已将我的功能简化为以下内容：

create function [dbo].[UserSuperTeams](@ProjectId int) 
returns table 
as 
return 
   if @ProjectId=0
   begin 
      select TeamId from TblTeam t 
        union
      select 0 as TeamId
   end
   else
   begin
      select t.TeamId from TblTeam t
        union
      select 1 as TeamId
   end;
go

我无法使其工作。似乎我有一些语法错误，但我不知道如何使其工作。有什么想法吗？

原文

I have simplified my function to the following:

create function [dbo].[UserSuperTeams](@ProjectId int) 
returns table 
as 
return 
   if @ProjectId=0
   begin 
      select TeamId from TblTeam t 
        union
      select 0 as TeamId
   end
   else
   begin
      select t.TeamId from TblTeam t
        union
      select 1 as TeamId
   end;
go

I cannot make it work.. It seems I have some syntax errors, but I cannot figure out how to make it work.. Any idea?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伏妖词 2024-08-22 04:22:59

如果要在函数中使用 t-sql 代码，则需要在“returns”部分定义表，然后使用插入语句填充它：

create function [dbo].[UserSuperTeams](@ProjectId int) 
  returns @results table (
    TeamId int
  ) as begin

  if @ProjectId=0 begin       
    insert @results (TeamId)
      select TeamId from TblTeam t
      union      
      select 0 as TeamId   
  end   
  else begin
    insert @results (TeamId)
      select t.TeamId from TblTeam t
      union      
      select 1 as TeamId   
end;

return
end

If you are going to use t-sql code in the function, you need to define the table in the 'returns' section, then populate it with insert statements:

create function [dbo].[UserSuperTeams](@ProjectId int) 
  returns @results table (
    TeamId int
  ) as begin

  if @ProjectId=0 begin       
    insert @results (TeamId)
      select TeamId from TblTeam t
      union      
      select 0 as TeamId   
  end   
  else begin
    insert @results (TeamId)
      select t.TeamId from TblTeam t
      union      
      select 1 as TeamId   
end;

return
end

回复收藏 0 原文

我纯我任性 2024-08-22 04:22:59

您必须在函数声明中使用临时名称和架构来声明表，然后在函数中将其插入：

create function [dbo].[UserSuperTeams](@ProjectId int) 
returns @mytable table (TeamID int)  
as 
...

然后类似：

INSERT INTO @mytable 
select t.TeamId from TblTeam t
    union
select 1 as TeamId

这对于向表中插入多行的函数特别有效。

或者，如果您只想返回单个 SELECT 的结果，则可以使用内联返回：

BEGIN
    RETURN (
        select t.TeamId from TblTeam t
            union
        select 1 as TeamId
    )
END

You must declare the table with a temporary name and a schema in the function declaration, then insert into it in the function:

create function [dbo].[UserSuperTeams](@ProjectId int) 
returns @mytable table (TeamID int)  
as 
...

and then something like:

INSERT INTO @mytable 
select t.TeamId from TblTeam t
    union
select 1 as TeamId

This works especially well for functions that insert several rows into the table.

Alternatively, if you only wish to return the results of a single SELECT, you can use an inline return:

BEGIN
    RETURN (
        select t.TeamId from TblTeam t
            union
        select 1 as TeamId
    )
END

回复收藏 0 原文

゛时过境迁 2024-08-22 04:22:59

正如 Jeremy 所说，或者如果它确实非常像您的简化示例，您可以这样做：（

create function [dbo].[UserSuperTeams](@ProjectId int) 
returns table 
as 
return (select TeamId from TblTeam t 
        union
        select CASE WHEN @ProjectId = 0 THEN 0 ELSE 1 END as TeamId
       )
go

即您可能不必定义表 var/schema）

As Jeremy said, or if it really is very like your simplified example you can do:

create function [dbo].[UserSuperTeams](@ProjectId int) 
returns table 
as 
return (select TeamId from TblTeam t 
        union
        select CASE WHEN @ProjectId = 0 THEN 0 ELSE 1 END as TeamId
       )
go

(i.e. you may not have to define the table var/schema)

回复收藏 0 原文

心碎的声音 2024-08-22 04:22:59

这段代码对我有用：

DROP FUNCTION IF EXISTS [dbo].[test] 
GO

CREATE FUNCTION [dbo].[TEST] 
(
	@ACTIVEONLY bit
)
RETURNS @TST TABLE (column1 char)
AS
BEGIN
	IF @ACTIVEONLY = 1
	BEGIN
		INSERT INTO @TST(column1) VALUES('A')
	END
	ELSE
	BEGIN
		INSERT INTO @TST(column1) VALUES('B')
	END
	RETURN
END
GO

SELECT * FROM [dbo].[TEST](1)
GO

SELECT * FROM [dbo].[TEST](0)
GO

this code is working for me :

DROP FUNCTION IF EXISTS [dbo].[test] 
GO

CREATE FUNCTION [dbo].[TEST] 
(
	@ACTIVEONLY bit
)
RETURNS @TST TABLE (column1 char)
AS
BEGIN
	IF @ACTIVEONLY = 1
	BEGIN
		INSERT INTO @TST(column1) VALUES('A')
	END
	ELSE
	BEGIN
		INSERT INTO @TST(column1) VALUES('B')
	END
	RETURN
END
GO

SELECT * FROM [dbo].[TEST](1)
GO

SELECT * FROM [dbo].[TEST](0)
GO

回复收藏 0 原文

~没有更多了~