Technical Article

Use Of Cursor Vs Simple While Loop

,

Copy and Paste both the scripts and run it in master database.

I prefer using WHILE Loop as a better programming practice always suggest writing scripts with Less Line Of Codes (if possible) and also use of CURSOR is reducing now due to higher consumption of MEMORY and if someone misses to include Deallocate Cursor it can still grab the memory and wont release.

/ * Get the Report of the Linked Servers Using CURSOR. */
SET NOCOUNT ON
DECLARE @LinkServername1 table (name sysname) 
DECLARE @name_linked_server CHAR(20),
@LinkServers VARCHAR(2)
, @LinkServername VARCHAR(600)

--INSERT @sysAdmin exec sp_helpsrvrolemember 'sysadmin' Changed to IS_SRVROLEMEMBER('sysadmin', name), 
INSERT INTO @LinkServername1
SELECT NAME FROM sys.servers WHERE is_linked ='1'

DECLARE LinkServername1_cursor CURSOR FOR SELECT name from @LinkServername1

Open LinkServername1_cursor


FETCH NEXT FROM LinkServername1_cursor INTO 
@name_linked_server

WHILE (@@FETCH_STATUS = 0) 
BEGIN
      PRINT '        Linked Server DETAILS- ' + @name_linked_server

      FETCH NEXT FROM LinkServername1_cursor INTO 
@name_linked_server


END

CLOSE LinkServername1_cursor
DEALLOCATE LinkServername1_cursor
SET NOCOUNT OFF




/ * Get the Report of the Linked Servers Using WHILE in a much simplier way . */
declare @i int = 0
,@LinkServers1 VARCHAR(2)
, @LinkServername10 VARCHAR(600);

SET @LinkServers1 = (SELECT COUNT(*) FROM sys.servers WHERE is_linked ='1')
while @i <= @LinkServers1
begin
     SELECT @LinkServername10 = name FROM sys.servers WHERE is_linked ='1' and server_id = @i
 PRINT '        Linked Server DETAILS- ' +@LinkServername10 
 select @i = @i + 1
end

Rate

2.39 (18)

You rated this post out of 5. Change rating

Share

Share

Rate

2.39 (18)

You rated this post out of 5. Change rating