IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'sp_UpdateCardFileRemoteServer')
                    AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE [dbo].[sp_UpdateCardFileRemoteServer]
GO
CREATE PROCEDURE [dbo].[sp_UpdateCardFileRemoteServer]
 @cardfileid uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;

 UPDATE cfrs
 SET cfrs.NeedUpdate = 1
	, cfrs.PatientID = null
 from [VCSN].[T_CARD_FILE_REMOTE_SERVER] cfrs
 WHERE cfrs.NeedUpdate = 0 AND cfrs.CardFile = @cardfileid
 and exists (select 1 from VCSN.T_REMOTE_SERVERS rs where rs.IsActive = 1 and rs.RemoteServersID = cfrs.RemoteServers)

INSERT INTO [VCSN].[T_CARD_FILE_REMOTE_SERVER] (CardFileRemoteServerID, CardFile, RemoteServers, NeedUpdate, PatientId)
SELECT NEWID(), @cardfileid, rs.RemoteServersID, 1, null
FROM [VCSN].[T_REMOTE_SERVERS] rs 
WHERE not exists (select * from [VCSN].[T_CARD_FILE_REMOTE_SERVER] cfrs where cfrs.RemoteServers = rs.RemoteServersID and cfrs.CardFile = @cardfileid)
    and rs.IsActive = 1
END
GO