Question: I would like to change my stored procedure so it is no longer utilizing a cursor to function. How could I write this stored procedure better?

I am using a cursor to compile dynamic SQL to query servers that I am creating from a list to track transaction log backup history.

```
ALTER PROC [dbo].[spLogBackup]

AS

TRUNCATE TABLE dbo.tLogBackup

DECLARE servers_cursor CURSOR
FOR
select  distinct LinkedserverName
from dbo.Environment
join master..sysservers on srvname COLLATE DATABASE_DEFAULT  = LinkedserverName COLLATE DATABASE_DEFAULT
where LinkedServerName not in ('TestDB')

and ServerUse in ('PROD', 'DR')
and IncludeInstats = 1
order by LinkedServerName

OPEN servers_cursor
DECLARE @Servername varchar(250)

DECLARE @sql varchar(8000)

FETCH NEXT FROM servers_cursor INTO @ServerName
WHILE (@@FETCH_STATUS = 0)
BEGIN
    --Looping throught the servers.
    set @sql = 'insert into dbo.tLogBackup (servername, rundate, runtime, jobname, Status, CurrentStatus, enabled) select ''' + @ServerName + ''',last_run_date, last_run_time, name, last_run_outcome, current_execution_status, enabled
     from openquery([' + @ServerName + '],''exec msdb.dbo.sp_get_composite_job_info'' )'
    print @ServerName
    exec (@sql)
    --SELECT (@sql)
  FETCH NEXT FROM servers_cursor INTO @ServerName
END
CLOSE servers_cursor
DEALLOCATE servers_cursor

```

Answer: 
Cursors are often condemned, and often rightly so. SQL tends to work best when processing what some have called "bags" of data; cursors lend themselves to row-by-row processing. If you can re-write something that's currently using row-by-row processing in SQL so it is all being handled by a single run of a query, it's almost always going to improve performance.

However, in this case, your cursor is identifying multiple remote servers, so it can send each of those servers a request. There isn't a way (that I know of) to send a request to multiple linked servers at once. So, the usual argument against cursors doesn't apply here.

It's possible there are faster methods to get what you want (for example, using an SSIS package to pull the data from the various other servers, where you might be able to pull from several in parallel), there's nothing wrong with using a cursor here, and functionally any method is probably going to use the same mechanism.
For this type of operation you need the cursor. There are solutions out there to simplify your code but they also use cursors under the covers.

However, if you just want to simplify your stored procedure you can do that. I would create a stored procedure that accepts a string of dynamic sql (pre formatted) and then executes it on those machines. This procedure has no knowledge of business logic or table structures.

I would then create a second stored procedure (spLogBackup) that the only thing it does is create the string and then pass it off to the other stored procedure to execute.

By separating the code in to two procedures you make the first one reusable for other queries that will need to do something similar. This will reduce code duplication and make creating similar processes in the future much simpler.