Creating a Stored Procedure

Here is a cursor that was written and I would like to turn it into a Stored Procedure and add functionality.  This CURSOR UPDATES a table that we will not be using anymore.  

DECLARE @client1 nvarchar(50); 

DECLARE CLients CURSOR FOR 

SELECT PROLAW_CODE FROM CMS_CLIENT WHERE CLNT_CLASS_CODE = 'ENTY' AND New = 'Yes'

OPEN Clients 
FETCH NEXT FROM Clients INTO @client1 

WHILE @@FETCH_STATUS = 0 
BEGIN 
	DECLARE @name1 nvarchar(max); 
	
	SET @name1 = (SELECT TOP 1 ShortDesc FROM Matters WHERE Matters IN (SELECT Matters FROM MattersContacts WHERE Contacts = @client1 AND IsMain = 'Y' AND MtoCClass = 'Entity')) 

	IF @name1 IS NOT NULL AND @name1 <> '' 
		BEGIN 
			UPDATE CMS_CLIENT 
			SET NAME = NAME + '-' + @name1 
			WHERE PROLAW_CODE = @client1 
		END 
	FETCH NEXT FROM Clients INTO @client1 
END 

CLOSE Clients DEALLOCATE Clients

Select allOpen in new window

I want to UPDATE data in another table that is on another server.  Here is what I would like to do, or something similar.  I don't want to use a CURSOR.  I just don't know how to loop through a stored procedure.  Also I only need to update when something has changed but I won't know what has changed until I compare values like in my WHERE clause.

DECLARE @client1 nvarchar(50); 
DECLARE @LastName nvarchar(50); 
DECLARE @FirstName nvarchar(50); 

DECLARE CLients CURSOR FOR 

SELECT CONTACTS, LASTNAME, FIRSTNAME FROM CONTACTS WHERE ContactClass = 'Entity'

OPEN Clients 
FETCH NEXT FROM Clients INTO @client1, @LastName, @FirstName 

WHILE @@FETCH_STATUS = 0 
BEGIN 
	DECLARE @name1 nvarchar(max); 
	DECLARE @EntityName nvarchar(max); 
	
	SET @name1 = (SELECT TOP 1 ShortDesc FROM Matters WHERE Matters IN (SELECT Matters FROM MattersContacts WHERE Contacts = @client1 AND IsMain = 'Y' AND MtoCClass = 'Entity')) 

	IF @name1 IS NOT NULL AND @name1 <> '' 
		BEGIN  
			SET @EntityName = @LastName + ', ' + @FirstName + '-' + @name1
		END 
	ELSE
		BEGIN
			@EntityName = @LastName + ', ' + @FirstName
		END

	UPDATE hbmc
	SET hbmn.Name =  CAST(@EntityName as varchar(120)) as PLContactName120, 
	hbmn.Name_Sort = CAST(@EntityName as varchar(30)) as PLContactName30, 
	hbmc.Client_Name = CAST(@EntityName as varchar(40)) as PLContactName40

	FROM Contacts c
	JOIN [MyServer].[MyDB].[dbo].HBM_Client hbmc ON c.CONTACTS = hbmc._ProLaw_FK 
	JOIN [MyServer].[MyDB].[dbo].HBM_Name hbmn ON hbmn.Name_Uno = hbmc.Name_Uno 
	JOIN [MyServer].[MyDB].[dbo].HBM_Address hbma ON hbma.Name_Uno = hbmc.Name_Uno 
	WHERE c.Contacts = @client1
	AND hbmn.Name <> CAST(@EntityName as varchar(120)) 
	OR hbmn.Name_Sort <> CAST(@EntityName as varchar(30)) 
	OR hbmc.Client_Name <> CAST(@EntityName as varchar(40))


	FETCH NEXT FROM Clients INTO @client1, @LastName, @FirstName  
END 

CLOSE Clients 
DEALLOCATE Clients

Select allOpen in new window