ALTER PROCEDURE [dbo].[AgentAction] 
@Option [int],
@pAgentID [int],
@pAgentName [nvarchar](100),
@pAgentType [int],
@pWorkTime [nvarchar](20),
@pNEP [nvarchar](10),
@pSite [nvarchar](20),
@pPhone [nvarchar](100),
@pParentAgentID [int],
@pSLANo [int],
@pPayNeed [bit],
@pDocRules [nvarchar](100),
@pNote [nvarchar](100),
@pCreateUser [int],
@pAddressName [nvarchar](20),
@pTown [nvarchar](50),
@pAddress [nvarchar](50)

AS

BEGIN

IF @Option = 0
 SELECT a.AgentID, a.AgentName, a.AgentType, at.AgentTypeName, a.WorkTime, a.NEP, a.[Site], a.Phone, a.ParentAgentID, a1.AgentName as ParentAgentName, a.SLANo, a.PayNeed, 
        a.DocRules, a.Note,
		CASE WHEN a.CreateUser = 0 THEN 'Сисадмин' ELSE c1.FIO END FIO, ad.AddressName, ad.Town, ad.[Address]
 FROM  dbo.Agents a left outer join dbo.Agents a1 on a1.AgentID = a.ParentAgentID
                    left outer join dbo.Users u1 on u1.UserID = a.CreateUser
				    left outer join dbo.Contacts c1 on c1.ContactID = u1.ContactID
 				    inner join dbo.AgentTypes at on at.AgentType = a.AgentType
					left outer join dbo.Addresses ad on ad.AgentID = a.AgentID
 WHERE (a.NEP like @pNEP)
       and (a.AgentName like @pAgentName)
	   and (a.Phone like @pPhone)
	   and ((ad.AddressName like @pAddressName) or (ad.AddressName is null))
	   and ((ad.Town like @pTown) or (ad.Town is null))
	   and ((ad.[Address] like @pAddress) or (ad.Address is null))
	   and (a.[Site] like @pSite)
 ORDER BY a.AgentName

IF @Option = 5
 SELECT a.AgentID, a.AgentName, a.AgentType, at.AgentTypeName, a.WorkTime, a.NEP, a.[Site], a.Phone, a.ParentAgentID, a1.AgentName as ParentAgentName, a.SLANo, a.PayNeed, 
        a.DocRules, a.Note,
		CASE WHEN a.CreateUser = 0 THEN 'Сисадмин' ELSE c1.FIO END FIO, '' as AddressName, '' as Town, '' as [Address]
 FROM  dbo.Agents a left outer join dbo.Agents a1 on a1.AgentID = a.ParentAgentID
                    left outer join dbo.Users u1 on u1.UserID = a.CreateUser
				    left outer join dbo.Contacts c1 on c1.ContactID = u1.ContactID
 				    inner join dbo.AgentTypes at on at.AgentType = a.AgentType
 ORDER BY a.AgentName

IF @Option = 6
 SELECT a.AgentID, a.AgentName, a.AgentType, at.AgentTypeName, a.WorkTime, a.NEP, a.[Site], a.Phone, a.ParentAgentID, a1.AgentName as ParentAgentName, a.SLANo, a.PayNeed, 
        a.DocRules, a.Note,
		CASE WHEN a.CreateUser = 0 THEN 'Сисадмин' ELSE c1.FIO END FIO, '' as AddressName, '' as Town, '' as [Address]
 FROM  dbo.Agents a left outer join dbo.Agents a1 on a1.AgentID = a.ParentAgentID
                    left outer join dbo.Users u1 on u1.UserID = a.CreateUser
				    left outer join dbo.Contacts c1 on c1.ContactID = u1.ContactID
 				    inner join dbo.AgentTypes at on at.AgentType = a.AgentType
 WHERE (a.NEP like @pNEP)
       and (a.AgentName like @pAgentName)
	   and (a.Phone like @pPhone)
	   and (a.[Site] like @pSite)
 ORDER BY a.AgentName

IF @Option = 7
 SELECT a.AgentID, a.AgentName, a.AgentType, at.AgentTypeName, a.WorkTime, a.NEP, a.[Site], a.Phone, a.ParentAgentID, a1.AgentName as ParentAgentName, a.SLANo, a.PayNeed, 
        a.DocRules, a.Note,
		CASE WHEN a.CreateUser = 0 THEN 'Сисадмин' ELSE c1.FIO END FIO, '' as AddressName, '' as Town, '' as [Address]
 FROM  dbo.Agents a left outer join dbo.Agents a1 on a1.AgentID = a.ParentAgentID
                    left outer join dbo.Users u1 on u1.UserID = a.CreateUser
				    left outer join dbo.Contacts c1 on c1.ContactID = u1.ContactID
 				    inner join dbo.AgentTypes at on at.AgentType = a.AgentType
 WHERE (a.AgentID <> @pAgentID)
       and (a.NEP like @pNEP)
       and (a.AgentName like @pAgentName)
	   and (a.Phone like @pPhone)
	   and (a.[Site] like @pSite)
 ORDER BY a.AgentName

BEGIN TRANSACTION


IF @Option = 1
 INSERT INTO dbo.Agents
 VALUES (@pAgentName, @pAgentType, @pWorkTime, @pNEP, @pSite, @pPhone, @pParentAgentID, @pSLANo, @pPayNeed, @pDocRules, @pNote, @pCreateUser)
 SELECT SCOPE_IDENTITY() as IDENT

IF @Option = 2
 DELETE FROM dbo.Agents

IF @Option = 3
 DELETE FROM dbo.Agents WHERE AgentID = @pAgentID
  
IF @Option = 4
 UPDATE dbo.Agents
 SET AgentName = @pAgentName,
     AgentType = @pAgentType,
     WorkTime = @pWorkTime,
     NEP = @pNEP,
     [Site] = @pSite,
     Phone = @pPhone,
     ParentAgentID = @pParentAgentID,
     SLANo = @pSLANo,
     PayNeed = @pPayNeed,
     DocRules = @pDocRules,
     Note = @pNote
 WHERE AgentID = @pAgentID

COMMIT TRANSACTION

END

            UniMainModule.spAgents.Prepared := true;
            UniMainModule.spAgents.Parameters.ParamByName('@Option').Value := 4;
            UniMainModule.spAgents.Parameters.ParamByName('@pAgentID').Value := fAddAgent.AgentCode;
            UniMainModule.spAgents.Parameters.ParamByName('@pAgentName').Value := fAddAgent.AgentName;
            UniMainModule.spAgents.Parameters.ParamByName('@pAgentType').Value := fAddAgent.AgentType;
            UniMainModule.spAgents.Parameters.ParamByName('@pWorkTime').Value := fAddAgent.WorkTime;
            UniMainModule.spAgents.Parameters.ParamByName('@pNEP').Value := fAddAgent.NEP;
            UniMainModule.spAgents.Parameters.ParamByName('@pSite').Value := fAddAgent.Site;
            UniMainModule.spAgents.Parameters.ParamByName('@pPhone').Value := fAddAgent.Phone;
            UniMainModule.spAgents.Parameters.ParamByName('@pParentAgentID').Value := fAddAgent.ParentAgent;
            UniMainModule.spAgents.Parameters.ParamByName('@pSLANo').Value := fAddAgent.SLANo;
            UniMainModule.spAgents.Parameters.ParamByName('@pPayNeed').Value := fAddAgent.PayNeed;
            UniMainModule.spAgents.Parameters.ParamByName('@pDocRules').Value := fAddAgent.DocRules;
            UniMainModule.spAgents.Parameters.ParamByName('@pNote').Value := fAddAgent.Note;
            UniMainModule.spAgents.ExecProc;