SQL选择带有功能

Question

我有以下SQL语句：

SELECT 
    RTRIM(LTRIM(REPLACE(LAGKART.VARENUMMER,CHAR(2),'')))     AS ItemNo, 
    RTRIM(LTRIM(REPLACE(LAGKART.SXSON,CHAR(2),'')))       AS Season, 
    ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT1,CHAR(2),''))),'')   AS Variant1, 
    ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT2,CHAR(2),''))),'')   AS Variant2, 
    (SELECT * 
    FROM [dbo].[B2BGetSpringFinal] (LAGKART.VARENUMMER, 
            LAGKART.VARIANT1, 
            LAGKART.VARIANT2 
           )) AS SpringAvailable 
FROM 
    LAGKART 

但我得到这个错误：

消息170，级别15，状态1，行8
附近有语法错误''。

但如果我叫具有固​​定值的功能：

SELECT 
    RTRIM(LTRIM(REPLACE(LAGKART.VARENUMMER,CHAR(2),'')))     AS ItemNo, 
    RTRIM(LTRIM(REPLACE(LAGKART.SXSON,CHAR(2),'')))       AS Season, 
    ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT1,CHAR(2),''))),'')   AS Variant1, 
    ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT2,CHAR(2),''))),'')   AS Variant2, 
    (SELECT * 
    FROM [dbo].[B2BGetSpringFinal] ('6261', 
            'Black', 
            'S' 
            )) AS SpringAvailable 
FROM 
    LAGKART 

我得到了想要的结果。

任何想法？

溴 的Mads

Answer 1

在SQL Server 2000中，只有常量和@local_variables可以传递到表-VA赋予功能。在SQL 2005及更高版本中，这是固定的。您可以尝试使用标量函数来获取SpringAvailable列值，或者查看升级到更新的SQL Server版本。

Answer 2

如果您的服务器支持热膨胀系数，你可以试试这个：

WITH a as (
SELECT VARENUMMER, 
RTRIM(LTRIM(REPLACE(LAGKART.VARENUMMER,CHAR(2),'')))     AS ItemNo, 
RTRIM(LTRIM(REPLACE(LAGKART.SXSON,CHAR(2),'')))       AS Season, 
ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT1,CHAR(2),''))),'')   AS Variant1, 
ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT2,CHAR(2),''))),'')   AS Variant2, 
FROM LAGKART 
) 
select *, 
(SELECT * FROM [dbo].[B2BGetSpringFinal] (a.VARENUMMER, 
              a.VARIANT1, 
              a.VARIANT2 
             ))        AS SpringAvailable 
from a ; 

Answer 3

您可以使用APPLY（CROSS或OUTER）通过列（S）值（S）作为参数到一个函数：

SELECT RTRIM(LTRIM(REPLACE(LAGKART.VARENUMMER,CHAR(2),''))) AS ItemNo, 
     RTRIM(LTRIM(REPLACE(LAGKART.SXSON,CHAR(2),''))) AS Season, 
     ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT1,CHAR(2),''))),'') AS Variant1, 
     ISNULL(RTRIM(LTRIM(REPLACE(LAGKART.VARIANT2,CHAR(2),''))),'') AS Variant2, 
     SpringAvailable.* 
FROM LAGKART 
CROSS APPLY 
(
    SELECT * 
    FROM [dbo].[B2BGetSpringFinal] (LAGKART.VARENUMMER, LAGKART.VARIANT1,LAGKART.VARIANT2) 
) AS SpringAvailable