SQL Server：我需要将行值作为参数传递给存储过程中的表函数

I want to create a stored procedure with a table-valued function as a column. I want to use one of the other column values as the parameter for the function.

我想创建一个带有表值函数作为列的存储过程。我想使用其他列值之一作为函数的参数。

ALTER PROCEDURE [dbo].[AuditReportLeaseID]
    @leaseID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        a.assetID as Asset, a.Location, 
        CONVERT(VARCHAR, a.auditdate, 101) AS Date, 
        a.qtyaudit AS Qty, 
        c.classname AS Class, a.grade AS Grade, 
        a.serialnumber AS [S/N], 
        a.materialdescription AS Description, a.Notes, 
        (SELECT tf.AD 
         FROM fGetAuditDescrConcat(a.assetId) tf) AS AuditDescription   
    FROM
        audit a
    LEFT OUTER JOIN
        ORDER_DETAILS od ON a.assetID = od.assetId
    INNER JOIN
        class c ON a.classid = c.classid 
    WHERE
        a.classID = c.classID 
        AND a.leaseID = @leaseID
    ORDER BY
        class, grade, a.materialDescription
END

This procedure will return multiple rows and I want to use the value of the first column a.assetID (which is a varchar(64)) as the parameter to the fGetAuditDescrConcat function.

此过程将返回多行,我想使用第一列a.assetID(它是一个varchar(64))的值作为fGetAuditDescrConcat函数的参数。

Is this possible?

这可能吗?

2 个解决方案

#1

Your question is unclear, but I take it you might be looking for something like this:

你的问题不清楚,但我认为你可能正在寻找这样的东西:

    ALTER PROCEDURE [dbo].[AuditReportLeaseID]
     @leaseID int
    AS
    BEGIN
        SET NOCOUNT ON;
   SELECT * FROM
    (Select a.assetID
      from audit a
      left outer join ORDER_DETAILS od on a.assetID=od.assetId
      inner join class c on a.classid=c.classid where a.classID = c.classID and a.leaseID = @leaseID
      Order by class,grade,a.materialDescription
    ) a
    CROSS APPLY fGetAuditDescrConcat(a.assetID)

    END

#2

If your given query returns error like Subquery returns more than one row;

如果您的给定查询返回错误,如Subquery返回多行;

Try the modified on as below:

尝试修改如下:

ALTER PROCEDURE [dbo].[AuditReportLeaseID]
    @leaseID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        a.assetID as Asset, a.Location, 
        CONVERT(VARCHAR, a.auditdate, 101) AS Date, 
        a.qtyaudit AS Qty, 
        c.classname AS Class, a.grade AS Grade, 
        a.serialnumber AS [S/N], 
        a.materialdescription AS Description, a.Notes, 
        (SELECT TOP 1 tf.AD 
         FROM fGetAuditDescrConcat(a.assetId) tf) AS AuditDescription   
    FROM
        audit a
    LEFT OUTER JOIN
        ORDER_DETAILS od ON a.assetID = od.assetId
    INNER JOIN
        class c ON a.classid = c.classid 
    WHERE
        a.classID = c.classID 
        AND a.leaseID = @leaseID
    ORDER BY
        class, grade, a.materialDescription
END

#1