call stored procedure for each record

Question

Hello!

I have sql stored procedure which pass and return some values:

CREATE PROCEDURE [dbo].[GetMoney]
   @userId int,  
   @month DateTime
AS
BEGIN
  ....
  Select @something
END

I want to call GetMoney procedure for each user in users table and use results in sql query. How can i do it?

Answer 1

Instead of calling the procedure for each row it's better to refactor the procedure to user defined function and use cross apply. The result will be the same but the code will be easier to read, maintain understand and will perform better.

Answer 2

The whole purpose of a RDBMS is that they operate in sets. Instead of going per user process all the users in 1 shot, it will be many times faster

if you really want to go the row based route, load all the users in a cursor and the loop over the cursor and call the proc for each user...but doing that you are using the database like a magnetic tape file...databases operate in SETS use a set based solution

Answer 3

What you're after is better suited to being function than a procedure, but that means you have to define a return type.

CREATE FUNCTION [dbo].[GetMoney]
  @userId int,  
  @month DateTime
  RETURNS 
AS
BEGIN
  ....
  SELECT @result = @something

  RETURN @result
END

Then you'd use it like:

SELECT dbo.GetMoney(t.userid, t.month)

But I want to stress that you're thinking in procedural/OO programming approach in a SET based world, and do not recommend pursuing this. It won't perform well, so it won't scale as load increases.

Answer 4

OR alternatively, you could define your stored proc to take a table variable as an input. THen you can call the proc once sending all your users and it will process all the records you sent it in a set-based fashion. No need for a cursor, no need for a separate process for one record or many records. This of course will only work in SQl Server 2008.