SQL Server：如何调用 UDF（如果可用）？

发布于 2024-08-27 01:44:28 字数 734 浏览 6 评论 0原文

大多数系统都有可用的用户定义函数（UDF）。有些不会。我想使用 UDF（如果存在）：

SELECT 
    Users.*,
    dbo.UserGroupMembershipNames(Users.UserID) AS MemberOfGroupNames
FROM Users

否则回退到可接受的替代方案

SELECT
   Users.*,
   (SELECT TOP 1 thing FROM Something 
    WHERE Something.ID = Users.UserID) AS MemberGroupNames
FROM Users

怎么办？

我的第一次尝试，使用显而易见的解决方案，当然失败了：

SELECT 
    Users.*,
    CASE
    WHEN (OBJECT_ID('dbo.UserGroupMembershipNames') IS NOT NULL) THEN
           dbo.UserGroupMembershipNames(Users.UserID)
    ELSE (SELECT TOP 1 thing FROM Something 
          WHERE Something.ID = Users.UserID)
    END AS MemberOfGroupNames
FROM Users

出于我之外的原因

原文

Most systems will have a user-defined function (UDF) available. Some will not. i want to use the UDF if it's there:

SELECT 
    Users.*,
    dbo.UserGroupMembershipNames(Users.UserID) AS MemberOfGroupNames
FROM Users

Otherwise fallback to the acceptable alternative

SELECT
   Users.*,
   (SELECT TOP 1 thing FROM Something 
    WHERE Something.ID = Users.UserID) AS MemberGroupNames
FROM Users

How do?

My first attempt, using the obvious solution, of course failed:

SELECT 
    Users.*,
    CASE
    WHEN (OBJECT_ID('dbo.UserGroupMembershipNames') IS NOT NULL) THEN
           dbo.UserGroupMembershipNames(Users.UserID)
    ELSE (SELECT TOP 1 thing FROM Something 
          WHERE Something.ID = Users.UserID)
    END AS MemberOfGroupNames
FROM Users

for reasons beyond me

分享到QQ

分享到微博

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

发布评论

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

迟到的我 2024-09-03 01:44:28

这是因为引擎在解析或准备执行计划期间尝试绑定到 UDF。

您必须将其分成两个带有条件的语句（不幸的是，显然这在视图或内联表值函数中不起作用）：

IF (OBJECT_ID('dbo.UserGroupMembershipNames') IS NOT NULL)
SELECT  
    Users.*, 
    dbo.UserGroupMembershipNames(Users.UserID) 
    AS MemberOfGroupNames 
FROM Users 
ELSE
SELECT  
    Users.*, 
    (SELECT TOP 1 thing FROM Something  
          WHERE Something.ID = Users.UserID) 
    AS MemberOfGroupNames 
FROM Users

This is because the engine is attempting to bind to the UDF, either during the parsing or preparation of the execution plan.

You will have to split it into two statements with a conditional (obvisously this will not work in a view or inline table-valued function, unfortunately):

IF (OBJECT_ID('dbo.UserGroupMembershipNames') IS NOT NULL)
SELECT  
    Users.*, 
    dbo.UserGroupMembershipNames(Users.UserID) 
    AS MemberOfGroupNames 
FROM Users 
ELSE
SELECT  
    Users.*, 
    (SELECT TOP 1 thing FROM Something  
          WHERE Something.ID = Users.UserID) 
    AS MemberOfGroupNames 
FROM Users

回复收藏 0 原文

却一份温柔 2024-09-03 01:44:28

您可能必须首先检查该函数是否存在，然后选择调用或不调用它：

IF OBJECT_ID('dbo.UserGroupMembershipNames') IS NOT NULL
    SELECT 
        Users.*,
        dbo.UserGroupMembershipNames(Users.UserID) AS MemberOfGroupNames
    FROM Users
ELSE
    SELECT
        Users.*,
        (SELECT TOP 1 thing FROM Something 
         WHERE Something.ID = Users.UserID) AS MemberGroupNames
    FROM Users

我不认为可以将其捆绑在单个条件 SELECT 语句中。

You'd probably have to check for the existance of the function first, and then select either with or without calling it:

IF OBJECT_ID('dbo.UserGroupMembershipNames') IS NOT NULL
    SELECT 
        Users.*,
        dbo.UserGroupMembershipNames(Users.UserID) AS MemberOfGroupNames
    FROM Users
ELSE
    SELECT
        Users.*,
        (SELECT TOP 1 thing FROM Something 
         WHERE Something.ID = Users.UserID) AS MemberGroupNames
    FROM Users

I don't believe it's possible to bundle this up in a single, conditional SELECT statement.

回复收藏 0 原文

~没有更多了~