在 SELECT 语句中使用存储过程或函数

Question

我有几个关于如何使用此 SELECT 语句执行我想要的操作的问题，但主要问题是如何在 SELECT 语句中使用函数/存储过程。

我有类似下面例子的东西。

虽然移动位足够小，可以工作，但属性（力量、护甲、意志、敏捷）很复杂，因为数字字母组合出现的顺序没有限制，并且需要用于 4 个不同的字段。编写一个过程并重复使用 4 次而不是重复代码是有意义的。

我曾尝试编写一个用于移动的程序，但除了 EXEC testProcedure 之外似乎无法执行任何操作，甚至 PRINT( testProcedure 4 ) 似乎也不起作用。

有没有办法在查询字段上使用用户定义的函数/过程？

SELECT DISTINCT 
       Main.CardName, Main.CardType, Main.ProductSet, Main.Movement, Main.Actions, 
       Main.Strength, Main.Armor, Main.Will, Main.Dexterity, Main.Health, 
       Main.Potions, 
( 
    SELECT COUNT( DISTINCT KeywordName )
    FROM HeroesViewMultiLine
    WHERE CardIndex = Main.CardIndex
) AS KeywordCount,
(
    SELECT COUNT( DISTINCT AbilityName )
    FROM HeroesViewMultiLine
    WHERE CardIndex = Main.CardIndex and AbilityResource = 'Action'
) AS ActionCount, 
(
    SELECT COUNT( DISTINCT AbilityName )
    FROM HeroesViewMultiLine
    WHERE CardIndex = Main.CardIndex and AbilityResource = 'Potion' 
) AS SkillCount,
(
    --Movement
    ( 
        SELECT CASE 
            WHEN Main.Movement = 5 THEN 1
            WHEN Main.Movement = 6 THEN 1
            WHEN Main.Movement = 7 THEN 3
            WHEN Main.Movement = 8 THEN 3
            WHEN Main.Movement = 9 THEN 6
            WHEN Main.Movement = 10 THEN 6
            ELSE 0
        END
    )
    --Actions
    +(Main.Actions),
    --STR
    +(
        SELECT CASE 
            WHEN Main.Strength = 5 THEN 1
            
            ELSE 0
        END
    )
    --ARM
    +(SELECT CASE 
        WHEN Main.Armor = 5 THEN 1
        ELSE 0
    END)
    --WILL
    +(SELECT CASE 
        WHEN Main.Will = 5 THEN 1
        ELSE 0
    END)
    --DEX
    +(SELECT CASE 
        WHEN Main.Dexterity = 5 THEN 1
        ELSE 0
    END)
    --Health
    +(Main.Health)
    --Potions
    +(Main.Potions)
    --KeywordCount
    +(SELECT COUNT( DISTINCT KeywordName )
    FROM HeroesViewMultiLine
    WHERE CardIndex = Main.CardIndex)
    --ActionCount
    +(SELECT COUNT( DISTINCT AbilityName )
    FROM HeroesViewMultiLine
    WHERE CardIndex = Main.CardIndex and AbilityResource = 'Action')
    --SkillCount
    +(SELECT COUNT( DISTINCT AbilityName )
    FROM HeroesViewMultiLine
    WHERE CardIndex = Main.CardIndex and AbilityResource = 'Potion' )
) AS BONUSES

FROM HeroesViewMultiLine Main

Answer 1

有许多不同的方法可以在Select语句中获取存储过程的结果，例如创建临时表，创建表变量，使用函数等等。但是，这是在Select语句中运行存储过程的最简单方法。

例如，这是您要执行的SP：

EXEC [AdventureWorks].[dbo].[uspGetManagerEmployees] 5
GO

您可以在OpenRowset的帮助下运行以下选择语句并执行存储过程。

SELECT *
FROM OPENROWSET('SQLNCLI',
'server=localhost;trusted_connection=yes;',
'EXEC [AdventureWorks].[dbo].[uspGetManagerEmployees] 5')
GO

是的，确实很容易。当您使用OpenRowset运行Select语句时，如果您获得有关临时查询的错误，则可以运行以下命令来修复错误。

Msg 15281, Level 16, State 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO