SQL Server : I need to pass a row value as a parameter to table function in a stored procedure

Question

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.

Is this possible?

Sorry - it does not let me compile the proc. It is unhappy with the (a.assetid). Incorrect syntax near '.'. — mham99, Nov 24 '15 at 11:01

score 0 · Answer 1 · answered Nov 24 '15 at 03:55

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

Thanks- I tried the above but it is still unhappy with the parameter for the function. I get an error compiling "Invalid syntax near ".'." Sorry I should have stated this in my original post The main issue is with the parameter. — mham99, Nov 24 '15 at 11:08

score 0 · Answer 2 · answered Nov 24 '15 at 09:14

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

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

Thanks- I tried the above but it is still unhappy with the parameter for the function. I get an error compiling "Invalid syntax near ".'." Sorry I should have stated this in my original post The main issue is with the parameter. — mham99, Nov 24 '15 at 11:08

SQL Server : I need to pass a row value as a parameter to table function in a stored procedure

2 Answers2