Dinamik SQL içinde Sıralama Saklı Yordamları

SORU

29 EYLÜL 2008, PAZARTESİ

Dinamik SQL içinde Sıralama Saklı Yordamları

Bu saat geçmişte araştırma geçirdiğim bir konudur. Görünüşe göre benim için bir şey olmalı dolayısı ile modern RDBMS çözüm ama henüz bulamadım bir şey gerçekten adresleri ne görmek için bir inanılmaz ortak ihtiyacı herhangi bir Web veya Windows uygulaması bir veritabanı arka uç.

Dinamik sıralama bahsediyorum. Benim fantezi dünyasında, bir şey gibi bu kadar basit olmalı:

ORDER BY @sortCol1, @sortCol2

Bu kurallı örnek İnternet üzerinden forumları her yerinde acemi SQL ve Stored Procedure geliştiriciler tarafından verilir. "Neden bu mümkün değil mi?" diye sorarlar. Her zaman, birisi sonunda geldi ders onlar hakkında derlenmiş doğanın saklı yordamları yürütme planları içinde genel ve her türlü diğer nedenler neden değil olası bir parametre doğrudan içine bir ORDER BY fıkra.

Bazılarınız zaten ne düşündüğünü biliyorum: "istemci sıralama Yapalım o zaman." Doğal olarak, bu veritabanı iş yükünü azaltır. Bizim durumumuzda olsa da, bizim veritabanı sunucuları değil, hatta bir kırılma ter � zaman ve onlar olmasa bile çok çekirdekli veya henüz herhangi bir diğer sayısız gelişmeler için sistem mimarisi buna her 6 ayda bir. Bu nedenle tek başına, bizim veritabanları sıralama aynı işi görür. bir sorun olmaz. Ayrıca, veritabanları vardırçoksıralama iyi. Onlar için optimize edilmiş ve sahip yıl oluyor değil mi, dilin ne derece esnek, sezgisel, basit ve her şeyden önce herhangi bir acemi SQL yazan bilir nasıl ve daha da önemlisi biliyorlar nasıl bir düzenleme, değişiklik, bakım, vb. Veritabanlarını vergi olmaktan çıkmış, sadece basitleştirmek ve kısaltmak!) istiyorum geliştirme zaman bariz bir seçim gibi görünüyor bu.

Web sorunu var. Oynadım etrafında JavaScript ile bunu yapacak istemci tarafında sıralama HTML tablolar, ama kaçınılmaz değil mi yeterince esnek vardır ve yine beri benim veritabanları değil aşırı vergi ve yapabilir sıralama gerçektengerçektenkolay, zor zamanlar yeniden yazmak veya roll-benim-kendi için alacaktı zaman haklı zorundayım sorter JavaScript. Aynı genellikle sunucu tarafı zaten JavaScript üzerinde muhtemelen daha tercih edilir olsa da sıralama için de geçerli. Özellikle Veri yükünü seven biri değilim, beni dava.

Ama bu geri mümkün-değil mi bu noktaya getiriyor; ya da daha doğrusu, kolay değil. Önceki sistemler, son derece dinamik sıralama almanın yolu hack ile yaptım. Güzel, ne sezgisel, basit, ya da esnek değildi ve Acemi SQL yazar saniye içinde kaybolacak. Zaten bu kadar çok "ama" karışıklık. "değil, bir çözüm olmadığını

Aşağıdaki örnekler ya da iyi bir kodlama stili ya da herhangi bir şey en iyi uygulamalar her türlü ifşa etmek demek değildir, ne de T-SQL programcısı olarak yeteneklerimi işaret ediyorlar. Ve ben kafasını tam olarak neyi kabul edecek, kötü form ve düz kesmek sadece.

Bir saklı yordam için parametre olarak bir tamsayı değer verdiğimiz (hadi parametresi Ara "") sıralama ve diğer değişkenler bir grup belirliyoruz. Örneğin... hadi sıralama 1 olduğunu söylüyorlar (veya varsayılan):

DECLARE @sortCol1 AS varchar(20)
DECLARE @sortCol2 AS varchar(20)
DECLARE @dir1 AS varchar(20)
DECLARE @dir2 AS varchar(20)
DECLARE @col1 AS varchar(20)
DECLARE @col2 AS varchar(20)

SET @col1 = 'storagedatetime';
SET @col2 = 'vehicleid';

IF @sort = 1                -- Default sort.
BEGIN
    SET @sortCol1 = @col1;
    SET @dir1 = 'asc';
    SET @sortCol2 = @col2;
    SET @dir2 = 'asc';
END
ELSE IF @sort = 2           -- Reversed order default sort.
BEGIN
    SET @sortCol1 = @col1;
    SET @dir1 = 'desc';
    SET @sortCol2 = @col2;
    SET @dir2 = 'desc';
END

Sen-ebilmek zaten görmek nasıl olursa ilan daha @colX değişkenleri tanımlamak için diğer sütunlar edebilirim gerçekten yaratıcı bir sütun için sıralama dayalı değer "tür"... kullanmak için, genellikle uçları yukarıya bakıyor gibi aşağıdaki inanılmaz derecede karışık fıkra:

ORDER BY
    CASE @dir1
        WHEN 'desc' THEN
            CASE @sortCol1
                WHEN @col1 THEN [storagedatetime]
                WHEN @col2 THEN [vehicleid]
            END
    END DESC,
    CASE @dir1
        WHEN 'asc' THEN
            CASE @sortCol1
                WHEN @col1 THEN [storagedatetime]
                WHEN @col2 THEN [vehicleid]
            END
    END,
    CASE @dir2
        WHEN 'desc' THEN
            CASE @sortCol2
                WHEN @col1 THEN [storagedatetime]
                WHEN @col2 THEN [vehicleid]
            END
    END DESC,
    CASE @dir2
        WHEN 'asc' THEN
            CASE @sortCol2
                WHEN @col1 THEN [storagedatetime]
                WHEN @col2 THEN [vehicleid]
            END
    END

Açıkçası bu çok soyunmuş bir örnektir. Gerçek şeyler, çünkü genelde dört ya da beş sütun için sıralama desteği üzerinde, her olası ikincil ya da üçüncü sütun için sıralama ek olarak (örneğin tarih inen akşam da ürüne göre adı artan) ve her destekleyen iki yönlü sıralama, hangi etkili bir şekilde iki katına sayıda vaka. Evet... kıllı çok çabuk oluyor.

Fikri" vehicleid gibi durumlarda storagedatetime önce sıralanmış alır sıralama değiştir... ama sözde esneklik, en azından bu basit örnekte, gerçekten orada bitiyor. "kolayca bir. Aslında, bir test sıralama yöntemi bu sefer geçerli değil, çünkü başarısız olan her durumda NULL değer vermektedir. Ve böylece fonksiyonları aşağıdaki gibi bir fıkra ile bitirmek:

ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah

Siz anladınız. SQL Server etkili bir order by yan null değerleri yoksayar, çünkü işe yarıyor. Bu SQL herhangi bir temel çalışma bilgisine sahip herkes muhtemelen gördüğünüz gibi inanılmaz derecede sabit tutmaktır. Eğer herhangi kaybettim, kendini kötü hissetme. Bize uzun bir zaman çalışma için aldı ve biz hala düzenleyebilir veya bu gibi yenilerini oluşturmaya karışık. Neyse ki sık sık değiştirilmesi gerekmez, aksi takdirde hızla "değil bu zorluğa değdi." olacaktı

Henüzyaptıiş.

Benim sorum:daha iyi bir yolu var mı?

Çözümler sadece gitmek için yol olabilir olmadığının farkındayım olarak Saklı Yordam olanlar, başka sorun yok. Tercihen, bilmek istiyorum eğer herkesten daha iyi yaparsın içinde Saklı Yordam, ama eğer değilse, nasıl bütün kolu bildirdiğiniz kullanıcı dinamik olarak sıralama tabloları veri (iki yönlü, çok) ASP.NET?

Ve okuma (ya da en azından kaymağını) için bu kadar uzun bir soru teşekkür ederim!

PS: dinamik sıralama, dinamik filtreleme/metin-arama sütunları, ROWNUMBER ile sayfalara ayrılmasını destekleyen bir saklı yordam benim örnek göstermedim mutlu Olun ()VEdeneyin...işlem hataları rollbacking ile yakalamak... "dev boyutlu" bile onları tarif etmeye yetmez.

Güncelleme:

Etmek istiyorumdinamik SQL kaçının. Birlikte bir dize ayrıştırma ve bir EXEC çalışan ilk etapta bir saklı yordam sahip olmanın amacı, bir sürü yendi. Bazen, eğer böyle bir şey yapmanın eksileri buna değer, en azından bu özel dinamik sıralama durumda olmaz eğer olsa merak ediyorum. Yine de, ben her zaman-gibi dinamik SQL dizeleri ne zaman yapsam; hala Klasik ASP dünyada yaşıyormuşum gibi kirli hissediyorum.
İlk etapta saklanan prosedürleri istiyoruz neden bir sürü içingüvenlik. Güvenlik sorunları üzerinde arama yapmak, çözümler önermek anlamıyorum. SQL Server 2005 ile şema düzeyinde izinleri gerekiyorsa her kullanıcı için ayrı ayrı () bireysel saklı yordamları ve tabloları karşı herhangi bir sorgu doğrudan inkar sonra da yapabiliriz. Artılarını ve eksilerini bu yaklaşımın eleştirme belki de başka bir soru için, ama bu benim kararım değil. Sadece kurşun kod maymun gibiyim. :)

CEVAP

29 EYLÜL 2008, PAZARTESİ

Evet, bu bir acı, ve bu şekilde yapıyorsun ki ne benzer:

order by
case when @SortExpr = 'CustomerName' and @SortDir = 'ASC' 
    then CustomerName end asc, 
case when @SortExpr = 'CustomerName' and @SortDir = 'DESC' 
    then CustomerName end desc,
...

Bu, benim için, hala DBA için ölçülebilirlik ve bakım kabusa döner kodundan dinamik SQL, bina çok daha iyi.

Koddan ne yapmam disk belleği yeniden Düzenle ve ben en azından tekrarı olmaz yani sıralama doldurma değerleri ile orada @SortExpr ve @SortDir.

SQL ile ilgili olarak, tasarım tutmak ve farklı saklı yordamları arasında aynı biçimlendirme, en azından düzgün ve değişiklikler yapmak için zaman tanınabilir.

Bunu Paylaş: