ALTER TABLE Subzonas DROP COLUMN CdRuta 
GO
ALTER TABLE Subzonas ADD Cd_Rut VARCHAR(4)
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[paInsSubzonas] @pmIdSzona VARCHAR(4),@pmSubzona VARCHAR(50),@pmIdZona VARCHAR(4),@pmCdRuta VARCHAR(4),@pmInactivo BIT
 AS 
 INSERT INTO Subzonas (IdSzona,Subzona,IdZona,Inactivo,Cd_Rut)
 VALUES (@pmIdSzona,@pmSubzona,@pmIdZona,@pmInactivo,@pmCdRuta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[paUpSubzonas] @pmIdSzona VARCHAR(4),@pmSubzona VARCHAR(50),@pmIdZona VARCHAR(4),@pmCdRuta VARCHAR(4),@pmInactivo BIT
 AS 
 UPDATE Subzonas SET Subzona=@pmSubzona,IdZona=@pmIdZona,Inactivo=@pmInactivo,Cd_Rut=@pmCdRuta
 WHERE IdSzona=@pmIdSzona
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[paQrySubzonas] @pmIdSzona VARCHAR(4)
 AS 
 SELECT IdSzona,Subzona,IdZona,Cd_Rut,Inactivo
 FROM Subzonas
 WHERE IdSzona=@pmIdSzona
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[paQrySubzonasLta] @pmIdZona VARCHAR(4)=Null
 AS 
  SELECT IdSzona,Subzona,S.IdZona AS CdZona,Zona,Cd_Rut,Ruta
  FROM Subzonas AS S INNER JOIN Zonas AS Z ON S.IdZona=Z.IdZona
  LEFT JOIN Rutas AS R ON S.Cd_Rut=R.IdRuta
  WHERE S.Inactivo=0 AND S.IdZona LIKE ISNULL(@pmIdZona,'%')
  ORDER BY Zona,Subzona
GO