iterate resultset in sql procedure

[sujithkumarsl]

How can i iterate each rows for my result set in a sql server stored procedure. My intention is to call a stored procedure in different databases and combine the result. Like

GetDatabases()
Foreach database -- I am here now
CallStoredProcedure()
StoreTheResultInTempDB()
Return the resultset

My small attempt...

[Mycroft Holmes]

Look into cursors [^]- they are evil but necessary for this type of processing First hit looks promising Alternative use a While loop[^]

[Niladri_Biswas]

Give a try with this... I made this based on my understanding

Declare @GetDBNames sysname
Declare @DynSql nvarchar(max)
declare @result table ([Database_Name] nvarchar(128), [Stored Procedure Name] sysname)

Declare DBNames cursor for
Select '['+name+']' from master.dbo.sysdatabases

open DBNames
FETCH NEXT FROM DBNames into @GetDBNames

WHILE @@FETCH_STATUS=0
BEGIN

SET @DynSql = '
Select Specific_Catalog as Database_Name, Routine_Name as ''Stored Procedure Name''
From '+ @GetDBNames+'.Information_Schema.Routines '

insert @result exec sp_executesql @DynSql

FETCH NEXT FROM DBNames into @GetDBNames
END
Close DBNames
Deallocate DBNames
select * from @result

It gives me all the stored procs names pertaining to the databases. Hope this helps. :)

Niladri Biswas