use build_file
go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


 

create PROCEDURE [dbo].[global_SearchPSGCAddress] 
 @SearchCriteria varchar(15),
 @Type int
 AS

IF (@Type=0)
BEGIN
  Select *
  From tbcoPSGCBarangays
  Where BARANGAY+', '+CITMUN like @SearchCriteria + '%'
  --Where BARANGAY like @SearchCriteria + '%'
END
  ELSE
IF (@Type=1)
BEGIN
  Select  PCODE+'-'+MCODE as PlaceCode,CITMUN,PROV
  From tbcoPSGCMunCity
	Where PCODE+'-'+MCODE = @SearchCriteria 
  --Where CITMUN like @SearchCriteria + '%'
END
  ELSE
IF (@Type=2)
BEGIN
  Select *
  From tbcoPSGCProvinces
  Where PROV like  @SearchCriteria + '%'
END

  ELSE
IF (@Type=3)
BEGIN
   Select  IsNull(PCODE,'')+'-'+IsNUll(MCODE,'') as PlaceCode,IsNULL (CITMUN,'')as Municipality,  IsNULL (PROV,'')as Province
  From tbcoPSGCMunCity
  Where (@SearchCriteria =IsNull(CITMUN,'')+', '+IsNull(PROV,'') or @SearchCriteria =IsNull(PCODE,'')+'-'+IsNull(MCODE,'')) or (@SearchCriteria=IsNull(CITMUN,'') or @SearchCriteria=IsNull(PROV,''))
END

IF (@Type=4)
BEGIN
  Select  IsNull(PCODE,'')+'-'+IsNUll(MCODE,'') as PlaceCode,IsNUll(CITMUN,'') as Municipality,IsNull(PROV,'') as Province 
  From tbcoPSGCMunCity
    Where CITMUN+', '+PROV like @SearchCriteria + '%' 
END


  ELSE
IF (@Type=5)
BEGIN
  Select   IsNUll(PCODE,'')as PCODE,IsNull(BARANGAY,'')+', '+IsNull(CITMUN,'') as Barangay
  From tbcoPSGCBarangays
  Where (@SearchCriteria=IsNull(BARANGAY,'')+', '+IsNull(CITMUN,'')or @SearchCriteria= IsNUll(PCODE,'') )

  --Where BARANGAY like @SearchCriteria + '%'
END
  ELSE
IF (@Type=6)
BEGIN
  Select  IsNUll(PCODE,'')as PCODE,IsNull(BARANGAY,'')+', '+IsNull(CITMUN,'') as Barangay,BARANGAY,CITMUN
  From tbcoPSGCBarangays
	 Where BARANGAY+', '+CITMUN like @SearchCriteria + '%'
END
	ELSE
IF (@Type=7)
BEGIN
  Select *
  From countries
  Where country_name like @SearchCriteria + '%'
 
END

	ELSE
IF (@Type=8)
BEGIN
  Select   IsNUll(country_code,'')as CCode,IsNull(country_name,'') as Country
  From countries
  Where (@SearchCriteria=IsNull(country_code,'')or @SearchCriteria= IsNUll(country_name,'') )

   
END
	ELSE
IF (@Type=9)
BEGIN
  Select   IsNUll(country_code,'')as CCode,IsNull(country_name,'') as Country
  From countries
  Where country_name like @SearchCriteria + '%' 

  
END