Notes

CREATE OR ALTER PROCEDURE [dbo].[SP3_Yerlesim_Listesi]
@pYerlesimYeriTuru INT,
@pAranacakIfade VARCHAR(10)
AS
BEGIN
DECLARE @cvBolgeNo INTEGER
DECLARE @cvBolgeAdi VARCHAR (100)

DECLARE @cvIlKodu VARCHAR(2)
DECLARE @cvIlAdi VARCHAR(100)

DECLARE @cvIlceKodu VARCHAR(100)
DECLARE @cvIlceAdi VARCHAR (100)

DECLARE @cvYerlesimYeriTuru VARCHAR(100)
DECLARE @cvYerlesimYeriID VARCHAR(100)
DECLARE @cvYerlesimYeriAdi VARCHAR(100)

DECLARE @SQL VARCHAR(MAX)

IF @pYerlesimYeriTuru = 1 -- BÖLGELER
BEGIN
DECLARE curBolgeler CURSOR FAST_FORWARD FOR
SELECT bolge_no, bolge_adi
FROM S_BOLGELER
WHERE pasif_mi=0
AND bolge_adi LIKE '%' +
SUBSTRING(@pAranacakIfade,1, 10) + '%'
ORDER BY bolge_adi;

OPEN curBolgeler
FETCH NEXT FROM curBolgeler
INTO @cvBolgeNo, @cvBolgeAdi
PRINT ('İçinde "' + @pAranacakIfade +
'" ifadesi geçen BÖLGELER:');
SET @SQL = '';
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = CAST(@cvBolgeNo AS VARCHAR) + '. ' + @cvBolgeAdi;
PRINT (@sql)
FETCH NEXT FROM curBolgeler
INTO @cvBolgeNo, @cvBolgeAdi
END
CLOSE curBolgeler
DEALLOCATE curBolgeler
END

ELSE IF @pYerlesimYeriTuru = 2 -- ILLER
BEGIN
DECLARE curIller CURSOR FAST_FORWARD FOR
SELECT il_kodu, il_adi
FROM S_ILLER
WHERE pasif_mi=0
AND il_adi LIKE '%' +
SUBSTRING(@pAranacakIfade,1, 10) + '%'
ORDER BY il_adi;

OPEN curIller
FETCH NEXT FROM curIller
INTO @cvIlKodu, @cvIlAdi
PRINT ('İçinde "' + @pAranacakIfade +
'" ifadesi geçen ILLER:');
SET @SQL = '';
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = CAST(@cvIlKodu AS VARCHAR) + '. ' + @cvIlAdi;
PRINT (@sql)
FETCH NEXT FROM curIller
INTO @cvIlKodu, @cvIlAdi
END
CLOSE curIller
DEALLOCATE curIller
END

ELSE IF @pYerlesimYeriTuru = 3 -- ILCELER
BEGIN
DECLARE curIlceler CURSOR FAST_FORWARD FOR
SELECT ilce_kodu, ilce_adi
FROM S_ILCELER
WHERE pasif_mi=0
AND ilce_adi LIKE '%' +
SUBSTRING(@pAranacakIfade,1, 10) + '%'
ORDER BY ilce_adi;

OPEN curIlceler
FETCH NEXT FROM curIlceler
INTO @cvIlceKodu, @cvIlceAdi
PRINT ('İçinde "' + @pAranacakIfade +
'" ifadesi geçen ILCELER:');
SET @SQL = '';
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = CAST(@cvIlceKodu AS VARCHAR) + '. ' + @cvIlceAdi;
PRINT (@sql)
FETCH NEXT FROM curIlceler
INTO @cvIlceKodu, @cvIlceAdi
END
CLOSE curIlceler
DEALLOCATE curIlceler
END

END