Prosedur Tersimpan Dimana Parameter Klausul

Saya memiliki halaman pencarian ASP.net tempat pengguna dapat memasukkan satu atau lebih kriteria pencarian. Halaman ini memanggil prosedur tersimpan untuk menanyakan db MS SQL Server 2008.

Bagian dari kriteria pencarian adalah tanggal tunggal atau rentang tanggal. Jika pengguna menyediakan Tanggal1, kami mencari pada satu tanggal. Jika pengguna menyediakan Tanggal1 dan Tanggal2, kami mencari berdasarkan rentang tanggal.

Masalah saya adalah mengkode logika ini di proses yang disimpan.

@Date1 datetime
@Date2 datetime
..other search params...

Jadi ada tiga syarat:

  1. @Date1 dan @Date2 keduanya null (pengguna tidak mencari tanggal)
  2. @Date1 bukan null dan @Date2 adalah null (pengguna mencari pada satu tanggal)
  3. @Date1 bukan null dan @Date2 bukan null (pengguna mencari rentang tanggal)

Saya tidak tahu bagaimana menyusun klausa WHERE untuk menangani masing-masing dari tiga kemungkinan kondisi.

Saya kenal dengan ISNULL() dan COALESCE()

Setiap tip atau saran sangat dihargai.


sql-server asp.net stored-procedures tsql where-clause
person paparush    schedule 08.06.2011    source sumber
comment
Bisakah Anda menunjukkan kueri yang Anda gunakan?   -  person Alex Aza    schedule 08.06.2011

Jawaban (2)


arrow_upward
4
arrow_downward 

CREATE PROCEDURE BLABLABLA(
  @DATE1 DATETIME = NULL,
  @DATE2 DATETIME = NULL
)
AS
BEGIN
  SELECT COL1, COL2
  FROM THE_TABLE
  WHERE 
    THE_TABLE.DATETIMEFIELD BETWEEN 
      ISNULL(@DATE1, THE_TABLE.DATETIMEFIELD) 
      AND COALESCE(@DATE2, @DATE1, THE_TABLE.DATETIMEFIELD)
END

Pilihan lain, yang kehilangan ekspresi tetapi kemungkinan menggunakan indeks, dapat berupa:

CREATE PROCEDURE BLABLABLA(
  @DATE1 DATETIME = NULL,
  @DATE2 DATETIME = NULL
)
AS
BEGIN
  SELECT COL1, COL2
  FROM THE_TABLE
  WHERE 
    (THE_TABLE.DATETIMEFIELD >= @DATE1 OR @DATE1 IS NULL) 
     AND (THE_TABLE.DATETIMEFIELD <= @DATE2 
         OR THE_TABLE.DATETIMEFIELD = @DATE1
         OR (@DATE1 IS NULL AND @DATE2 IS NULL)) 
END
person Rodrigo    schedule 08.06.2011
comment
Rodrigo, terima kasih atas jawabannya. Akankah ANTARA menangani kasus di mana hanya @Date1 yang diberikan? Artinya, pengguna hanya mencari di mana THE_TABLE.DATETIMEFIELD = @Date1? - person paparush; 08.06.2011
comment
jika @date2 adalah null, fungsi isnull akan jatuh ke kolom yang sama, sehingga klausa di mana akan menjadi datetimecolumn between @date1 and datetimecolumn, dan sebagai datetimecolumn = datetimecolumn, bagian terakhir dari klausa antar akan tetap terbuka untuk nilai apa pun yang mungkin - person Rodrigo; 08.06.2011
comment
jawabannya terlihat bagus, tetapi apakah penggunaan ISNULL akan mencegah kemungkinan penggunaan indeks pada DATETIMEFIELD ? (yaitu pertimbangan sargabilitas) - person Moe Sisko; 08.06.2011
comment
Oke, saya telah memperbaiki kuerinya sehingga kemungkinan besar akan menggunakan indeks di DATETIMEFIELD - person Rodrigo; 08.06.2011
comment
Saya masih melihat ada masalah. Jika @Date1 diberikan dan @Date2 adalah null, kode selalu melakukan ›= @Date1. Logika yang ingin saya buat adalah: jika hanya @Date1 yang diberikan maka di mana datetimecolumn = @Date1 - person paparush; 08.06.2011
comment
Untuk mencari pada satu tanggal, menurut saya yang akan saya lakukan hanyalah menginstruksikan pengguna untuk menyetel tanggal yang sama di @Date1 dan @Date2 . - person paparush; 08.06.2011
comment
Ok, sekarang pertanyaannya sudah diperbaiki untuk mengikuti spesifikasinya - person Rodrigo; 09.06.2011

arrow_upward
1
arrow_downward

Anda bisa mencoba membuat kueri SQL Anda sebagai string di SP dan kemudian menjalankannya, seperti ini:

...
declare @sql varchar(500)
set @sql = 'select from myTable where 1=1'

if(@Date1 <> null)
    set @sql = @sql + ' and date1 >= '+ @date1

if(@Date2 <> null)
    set @sql = @sql + ' and date2 <= '+ @date2

print(@sql) -- for debug 
exec(@sql)
person noinstance    schedule 08.06.2011
comment
hei, ini bukan praktik yang sempurna tetapi berhasil dan dalam beberapa kasus ini adalah cara paling sederhana dan efektif untuk membuat kueri. - person noinstance; 08.06.2011
comment
sql dinamis harus digunakan hanya jika tidak ada pilihan lain. Dalam hal ini, Anda dapat membuat ekspresi sql yang dapat menangani persyaratan tanpa sql dinamis apa pun. - person Rodrigo; 08.06.2011
comment
+1 untuk sql dinamis, Entity Framework dan Linq to Sql menghasilkan sql dinamis saat run time - person jimjim; 08.06.2011
comment
Saya ingin memberikan -1 untuk manipulasi string semacam ini, tetapi suaranya turun menjadi -2, postingan ini tidak buruk dan tidak bagus, tidak ingin turun terlalu banyak. Namun setuju juga dengan Rodrigo untuk tidak menggunakan sql dinamis jika tidak diperlukan. - person jimjim; 08.06.2011
comment
@Rodrigo, seperti yang saya akui solusi Anda lebih elegan dalam kasus khusus ini, saya tidak setuju dengan kata Must dalam pernyataan pertama Anda. SQL dinamis bisa lebih mudah dibaca dan di-debug dibandingkan ekspresi sql yang panjang dan kompleks. Saya belum pernah melakukan benchmarking tetapi sejauh yang saya tahu tidak ada ketidaknyamanan dalam menggunakan sql dinamis. - person noinstance; 08.06.2011
comment
sangat merepotkan? seperti injeksi sql, pengecualian runtime yang tidak terduga, kurangnya rencana eksekusi, dll... - person Rodrigo; 08.06.2011
comment
injeksi sql akan selalu ada jika data tidak dibersihkan. tentang argumen lain saya benar-benar tidak tahu, tidak pernah berurusan dengan itu. jadi terima kasih untuk topiknya. - person noinstance; 08.06.2011
comment
Ini biasanya merupakan cara yang paling efektif untuk melakukan pencarian kompleks dengan banyak parameter yang mungkin diisi atau tidak. Saya bukan penggemar SQl dinamis secara umum tetapi permintaan pencarian adalah keunggulannya. - person HLGEM; 08.06.2011
comment
Namun, jangan gunakan SQl dinamis sampai Anda membaca artikel ini:sommarskog.se/dynamic_sql.html - person HLGEM; 08.06.2011