question about calling a stored procedure 1

[mollytu]

Hi,

How do you call a stored procedure to run for every row in a table? For example:

EXEC myProc @field1, @field2, @outVar OUT, @outVar OUT

against all the rows in a given table?

Thank you

 

[bborissov]

Just define a proper WHERE clause in the SP and pass parameter to it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mollytu]

That is a good idea, but I'm not allowed to modify the sp.

 

[markros]

You may want to post the SP. If you want to return all rows, you may need to pass % for parameters, but it's hard to tell without knowing the SP code.

 

[mollytu]

What about just looping the proc through each row in the table? That was my idea.

 

[markros]

SQL Server is best when used as SET BASED (which means no looping).

Perhaps you can transform your SP into user defined function and if so, you would be able to run it for all records in a select statement.

 

[mollytu]

Markros, could you show a little example of what you mean... like an inline function?

 

[markros]

Depends on what your SP was doing, but here is a quick example

create function dbo.MinValue(field1 int, field2 int) return int
as 
  begin
    if field1 > field2
       return field2
    else
       return field1
   end

select T.Field1, T.Field2, dbo.MinValue(T.Field1, T.Field2) as MinValue from myTable

This is a very quick sample from the top of my head - not tested at all.