Run a proc on several different values of one parameter
- by WEFX
I have the following query that gets run within a proc. The function MyFunction returns a table, and this query joins on that table. This proc works great when a @MyArg value is supplied. However, I’m wondering if there’s a way to run this on all @MyArg values in the database. I’m sure there’s a way to do it within a loop, but I know that loops are generally to be avoided at the db layer.
I really just need to perform this for the sake of checking (and possibly cleansing) some bad data.
SELECT ColumnA, ColumnB, ColumnC
FROM
(
SELECT
a.ColumnA,
a.ColumnB,
a.ColumnC,
ROW_NUMBER()
over(partition by a.ColumnD order by f.ColumnX) as RowNum
FROM dbo.MyTableA AS a
INNER JOIN dbo.MyFunction(@MyArg) f ON f.myID = a.myID
WHERE (a.myBit = 1 OR a.myID = @MyArg)
) AS x
WHERE x.rownum = 1;
