CODEWITHSUNDEEP
sql-serverstored-procedures
sumanth
sumanth

Reputation: 65

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

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

Upvotes: 0

Views: 1736

Answers (2)

Devart
Devart

Reputation: 121922

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

Upvotes: 0

christofr
christofr

Reputation: 2700

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...

Upvotes: 4

Related Questions