SQL Server sp_executesql ile çalıştırılan sorguya tabloyu parametre olarak göndermek

[sabanakman]

İyi günler. SQL Server sistem prosedürlerinden olan sp_executesql ile dinamik string olarak oluşturulan sorguya değişkenler parametre olarak verilebilmektedir. Haliyle tablo tipli değişkenler için de aynı durum söz konusudur. Burada dikkat edilmesi gereken en önemli nokta parametre olarak verilecek tablonun tipi CREATE TYPE ... ile oluşturulmalıdır. Tablo tipli değişkenimizi oluşturduktan sonra iş artık normal değişkenlerin parametre olarak verildiği gibi yürütülcektir. Mesela örnek olması açısından aşağıdaki gibi bir tip oluşturalım.

CREATE TYPE TRaporTbl AS TABLE (Musteri nvarchar(50), Borc float, Alacak float)
-->DROP TYPE TRaporTbl

Oluşturduğumuz bu tipli bir tablo değişkeni tanımlayıp örnek kayıt girelim ve bu kayıtları (parametreye değişkenleri belirterek) sorgulayalım

declare @Rapor as TRaporTbl, @Sorgu nvarchar(max), @SinirdakiBorc float
set @SinirdakiBorc=50

insert into @Rapor (Musteri, Borc, Alacak) values ('Ali', 10, 20)
insert into @Rapor (Musteri, Borc, Alacak) values ('Veli', 50, 5), ('Mehmet', 120, 30)

set @Sorgu='
select * from (
  select Musteri, Borc, Alacak, isNull(Borc,0)-isNull(Alacak,0) as Bakiye from @tblRapor
) as tbl where Bakiye>@SinirdakiBorcMiktari'

execute sp_executesql @Sorgu, N'@SinirdakiBorcMiktari float, @tblRapor TRaporTbl READONLY', @SinirdakiBorcMiktari=@SinirdakiBorc, @tblRapor=@Rapor

Sorgu çalıştırıldığı zaman aşağıdaki gibi bir sonuç gelecektir.

Kod: (Select All)

Musteri   |    Borc    |    Alacak    |    Bakiye
------    |    ----    |    ------    |    ------
Mehmet    |    120     |    30        |     90

Örneğe göre uyguladığımız yöntem pek uygun düşmeyebilir fakat konunun anlaşılması için basit bir örnektir bu. Zira bazı uygulamalarda karmaşık ilişkiler ve yöntemlerle elde edilen omurga bir tabloyu elde ettikten sonra şekilden şekile sokarak çok değişik raporlar elde edilebilmektedir. Bu yöntem saklı yordamlara ağırlık verenler için hayat kurtarıcı olabilmektedir. İyi çalışmalar.

[hi_selamlar]

Paylaşım için teşekkürler.
Elinize sağlık.

[Abdullah ILGAZ]

Bundan 3 yıl önce StackOverFlow'a sorduğum bir soru vardı. Şimdi size iletebilirim sanırım; Sadece output parametresi olan bir stored procedure için dynamic sql nasıl kullanılır?

Link

[mrmarman]

Merhaba.
Soruyu çok beğendim.   Cevabı da merakla bekliyor olacağım.

Soruyu doğru anladıysam ben olsam ne yapardım diye kendime sorunca, bir VIEW oluşturup bu tekil paramtreyi o VIEW üzerinden sonuca çekerdim.

Diğer firma veritabanlarını ilgili VIEW / "başvuru tablosu" adını verelim yönlendirir, bu başvuru tablosu / VIEW ile takibini gözlemlemek ile hallederdim.

VIEW için eğer veritabanı kontrolü sizde ise diğer firma veritabanları dilerse ORACLE olsun dilerse başkası olsun, bağlantı parola vb. bilgileri ile  [FırmaA] [FirmaB] gibi isimler ile birer "Linked Server" kurar ( veya nazınız geçen BT DB adminleri varsa onlara bu LinkedServer bağlantılarını kurdurtur ) oradan gönül rahatlığıyla verileri sanki yereldeymiş gibi çekerdim.

LinkedServer olamıyorsa, OPENROWSET ile de FirmaA, FirmaB verilerini çekebilirsiniz.

Örnek için bkz. bu DelphiCan linki

[ssahinoglu]

(16-09-2018, Saat: 22:22)Abdullah ILGAZ Adlı Kullanıcıdan Alıntı: Bundan 3 yıl önce StackOverFlow'a sorduğum bir soru vardı. Şimdi size iletebilirim sanırım; Sadece output parametresi olan bir stored procedure için dynamic sql nasıl kullanılır?

Link

Tüm kodu tek EXECUTE içine almak yerine, her işlemi ayrı çalıştırmak belki işe yarayabilir.

EXECUTE "use ...."
EXECUTE "exec ...."

EXECUTE "exec ...."

[Abdullah ILGAZ]

(17-09-2018, Saat: 01:10)ssahinoglu Adlı Kullanıcıdan Alıntı: Tüm kodu tek EXECUTE içine almak yerine, her işlemi ayrı çalıştırmak belki işe yarayabilir.

EXECUTE "use ...."
EXECUTE "exec ...."

EXECUTE "exec ...."

Soruyu inceleme fırsatınız oldu mu?

[sabanakman]

İyi günler. Aslında use komutu daha çok sql sorgulama editörlerine ayar çekmek için kullanılmaktadır. Burada yapılması gereken ilgili veritabanı adının da dinamik olarak sorguya eklenmesi gerekliliğidir. Yani use .. satırını kullanmaya gerek duymadan [veritabanıadı].dbo.[spName] şeklinde kullanmak yeterli olacaktır. Örnekleyecek olursak bir out parametreli stored procedure tanımlayalım. Bende deneme isimli bir veritabanı vardı ve aşağıdaki gibi bir prosedür oluşturdum.

use deneme
go
CREATE PROCEDURE spCurrentYear @Year INT OUTPUT  
AS  
BEGIN
 SET @Year=Year(GetDate())
END

Daha sonra da aşağıdaki gibi dinamik bir sorgu ile başka bir veritabanında dahi olsam sorun olmadan kullanılabiliyor.

use master
go

declare @yili int
execute sp_executesql N'execute [deneme].[dbo].[spCurrentYear] @yil out', N'@yil int out', @yil=@yili out
select @yili

İyi çalışmalar.