execute sp in a select statement

For an instance I a select statement and it is returning 1000 rows. I need to execute a particular stored procedure for every row the the select statement is returning.

have you got any idea how can I do that?

From stackoverflow

• Construct the EXECUTE statements in your select like this:

  SELECT 'EXEC sp_whatever ' + parameter stuff
  FROM   your_table
  

  Then run the results! Alternatively, paste your results into a spreadsheet package, and use string concatenation to construct the EXEC statements - just create a formula and paste it down the 1,000 rows. I personally prefer the first approach.

  I am reading your "for an instance" as "this is a one-off task". If this is a task that needs automating, then one of the other answers may be the right approach.

• Disclaimer: I'm not sure if I understand your question correctly.

  Assuming you are on SQL Server 2005 upwards, you could create a table-valued user defined function and use the OUTER APPLY operator in your query.

  David Bonnici : any example? im a begineer

  Mehrdad Afshari : http://msdn.microsoft.com/en-us/library/ms175156.aspx

  Cipher : Thats really neat! thanks for posting.

  Mehrdad Afshari : Yes, it surely is. It was one of the many things I was seriously excited about in 2005.

• Use a User Defined Function?

• Most RDBMS will let you select rows from stored procedure result sets. Just put your stored procedures in the FROM clause, as you would for common table expressions. For instance:

  SELECT sp.ColumnInResultSet, t.BaseTableColumnName
  FROM sp_whatever ( Args) sp INNER JOIN BaseTable t ON t.ID = sp.ID;