sql - 将存储过程转换为表值查询

Question

我有这个程序

ALTER PROCEDURE [dbo].GetHerdByUserProc(@user int)
As 
    begin
        Declare
            @GroupId uniqueidentifier,
            @UserTrade bit

        Set @GroupId = (select tbUser.group_id from tbUser where Userid = @user)
        Set @UserTrade = (select tbUser.isTrade from tbUser where Userid = @user)
        if @GroupId IS NOT NULL and @UserTrade = '1'
        Begin 
            select HerdId from tbUserHerds where tbUserHerds.UserId in (select Userid from tbUser where tbUser.Group_Id = @GroupId)
            return;
        END
        If @GroupId IS NOT NULL
        Begin   
            select HerdId from tbUserHerds where tbUserHerds.UserId = @user
            return;
        End
        return;
    End

它正确地返回一个列表，除了我还想在返回的列表上运行查询，据我所知我不能写这样的查询

Select * from GetHerdByUserProc 80

所以我正在寻找将其转换为表值查询的最佳方法。

我已将更改更改为 'Create Function x(@user int) Returns Table As'

但这并不能正常工作，它开始向我尖叫错误。

有什么想法吗？数据库服务器是MSSQL2008

Answer 1

根据您的语法，我现在假设使用 SQL Server。

来自 BOL内联函数的语法应该是...

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

使用这种格式，您不能使用SET, DECLARE, IF, etc .您可以使用的只是一个 SQL 语句。 [如果您需要使用程序流，请查看多语句表值函数。]

这是一个单独的主题，但内联表值函数比其多语句等效函数具有许多性能效率。几乎总是，如果你可以内联，你就应该这样做。

碰巧，您可以在没有 IF 的情况下编写您的逻辑语句，并且只使用一个 SQL 语句。这给出了以下内联表值函数...

CREATE FUNCTION [dbo].GetHerdByUserProc(@user int)
RETURNS TABLE
RETURN
  SELECT
    herd.HerdID
  FROM
    tbUser          AS user
  INNER JOIN
    tbUser          AS group
      ON group.group_id = user.group_id
  INNER JOIN
    tbUserHerds     AS herd
      ON herd.UserID = group.UserID
  WHERE
        user.userID   = @userID
    AND user.isTrade  = 1
    AND user.group_id IS NOT NULL

  UNION ALL

  SELECT
    herd.HerdID
  FROM
    tbUser          AS user
  INNER JOIN
    tbUserHerds     AS herd
      ON herd.UserID = user.UserID
  WHERE
        user.userID    = @userID
    AND user.isTrade  <> 1
    AND user.group_id IS NOT NULL

UNION ALL结合 WHERE条款有效地做你的IF给你的陈述。 (请注意，如果 user.isTrade 可以是 NULL，您需要将 user.isTrade <> 1 更改为更类似于 ISNULL(user.isTrade, 0) <> 1 的内容。)

有可能，您甚至可以将其简化为单个查询，不过我会对其进行测试，看看它是否真的更有效率......

RETURN
  SELECT
    herd.HerdID
  FROM
    tbUser          AS user
  INNER JOIN
    tbUser          AS group
      ON (group.group_id = user.group_id AND user.isTrade = 1)
      OR (group.user_id  = user.userID)
  INNER JOIN
    tbUserHerds     AS herd
      ON herd.UserID = group.UserID
  WHERE
    user.userID = @userID

• 如果group_id为 NULL，第一次加入永远不会成功。
• 然后这两个 IF block 由 OR 模拟.