How can I call a function using a column value in each row as a parameter?

Question

I am using the following select statement in a stored procedure to get data from a table, with a function that returns a JSON for one of the columns. What am I doing wrong in my query?

The function GetRecordComments requires two parameters, with the first one being the RecordID of each row. When I use this query though, I get the following error. How can I call the function passing the recordID as parameter for each row?

Query:

SELECT 
    RercordID, Name, Region, 
    dbo.GetRecordComments((SELECT RecordID 
                           FROM RecordList  
                           WHERE RecordID = a.RecordID), 1),
    [Address],
    dbo.GetRecordComments((SELECT RecordID 
                           FROM RecordList  
                           WHERE RecordID = a.RecordID), 2)
FROM 
    RecordList AS a

Error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Why not .., Region, dbo.GetRecordComments(RecordID, 1), [Address], ..? — Fabio
– Fabio, Commented Mar 22, 2020 at 3:42

Gordon Linoff · Accepted Answer · 2020-03-22 12:53:06Z

1

To me, it is very strange that RecordId would be repeated in a table called RecordList. But clearly it is. Happily the subqueries are not necessary:

SELECT rl.RecordID, rl.Name, rl.Region, 
       dbo.GetRecordComments(rl.RecordID, 1),
       rl.Address,
       dbo.GetRecordComments(rl.RecordID, 2)
FROM RecordList rl;

You should know that such user-defined functions are generally performance killers. My guess is that you have a comments table. An explicit JOIN is usually much preferable.

answered Mar 22, 2020 at 12:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I call a function using a column value in each row as a parameter?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related