how to call parameterized stored procedure for every row in a table without using cursor

Question

My stored procedure accepts one parameter and returns single value . My requirement is that to call stored procedure to every row the table with company id . I tried this but it won't works

SELECT CompanyID,CompanyName,EmailID,'Exec Usp_GetCount' 

+CONVERT(varchar(256),companyID) 'Employees' from Tbl_Company

Thanks

possible duplicate of [SQL Call Stored Procedure for each Row without using a cursor](http://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor) — Alex K., May 07 '13 at 12:57
Can you not change the process to be more of a set based approach, this is not exactly the most efficient way to write SQL and your performance will be terrible. — Jason Carter, May 07 '13 at 13:10

score 4 · Accepted Answer · answered May 07 '13 at 13:01

4

Use a Function rather than a Stored Procedure in your Select - then you can do things like:

SELECT CompanyId, CompanyName, dbo.GetCount(CompanyId) 'Employees'
FROM Tbl_Company
WHERE...

answered May 07 '13 at 13:01

christofr

2,680
1
18
19

score 0 · Answer 2 · answered May 07 '13 at 13:06

Try this one -

If SP:

DECLARE @temp TABLE
(
      CompanyID INT
    , Cnt INT
)

DECLARE @CompanyID INT

DECLARE cur CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT DISTINCT CompanyID 
    FROM dbo.Tbl_Company

OPEN cur

FETCH NEXT FROM cur INTO @CompanyID

WHILE @@FETCH_STATUS = 0 BEGIN

    DECLARE @Result INT
    EXEC @Result = dbo.Usp_GetCount CONVERT(VARCHAR(256), @CompanyID)

    INSERT INTO @temp (CompanyID, Cnt)
    SELECT @CompanyID, @Result

    FETCH NEXT FROM cur INTO @CompanyID

END

CLOSE cur
DEALLOCATE cur

SELECT 
      c.CompanyID
    , c.CompanyName
    , c.EmailID
    , Employees = t.Cnt
FROM dbo.Tbl_Company c
JOIN @temp t ON t.CompanyID = c.CompanyID

If scalar function:

SELECT 
      c.CompanyID
    , c.CompanyName
    , c.EmailID
    , Employees = dbo.GetCount(c.CompanyID)
FROM dbo.Tbl_Company c

how to call parameterized stored procedure for every row in a table without using cursor

2 Answers2

Linked