Gespeicherte Prozedur mit optionalen "WHERE" -Parametern

Ich habe ein Formular, wo der Benutzer verschiedene Parameter zu Graben, durch einige Daten (status, Datum, etc.).

Ich erstelle eine Abfrage:

SELECT * FROM table WHERE:
status_id = 3
date = <some date>
other_parameter = <value>

etc. Jeder WHERE ist optional (kann ich wählen Sie alle Zeilen mit status = 3 oder alle Zeilen mit date = 10/10/1980 oder alle Zeilen mit status = 3 AND date = 10/10/1980 etc.).

Angesichts einer großen Anzahl der Parameter, die alle optional, was ist der beste Weg, um eine dynamische gespeicherte Prozedur?

Arbeite ich auf verschiedenen DB, wie:
MySQL, Oracle und SQLServer.

InformationsquelleAutor der Frage pistacchio | 2009-03-30

47

Ist eine der einfachsten Möglichkeiten, dies zu erreichen:
```
SELECT * FROM table 
WHERE ((@status_id is null) or (status_id = @status_id))
and ((@date is null) or ([date] = @date))
and ((@other_parameter is null) or (other_parameter = @other_parameter))
```
etc.
Dies beseitigt vollständig dynamisches sql und ermöglicht die Suche auf ein oder mehrere Felder. Durch den Wegfall von dynamischen sql-Sie entfernen noch ein weiteres Sicherheitsproblem in Bezug auf sql-injection.

InformationsquelleAutor der Antwort NotMe

Erstellen Sie Ihre Vorgehensweise so:

CREATE PROCEDURE [dbo].[spXXX]
    @fromDate datetime = null,
    @toDate datetime = null,
    @subCode int = null
as
begin
set NOCOUNT ON
/* NOCOUNT limits the server feedback on select results record count */
SELECT
    fields...
FROM
    source
WHERE
    1=1
--Dynamic where clause for various parameters which may or may not be passed in.
and ( @fromDate is null or [dateField] >= @fromDate)
and ( @toDate is null or [dateField] <= @toDate)
and ( @subCode is null or subCode= @leaveTypeSubCode)
order by fields...

Wird diese ermöglichen es Ihnen, um die Prozedur auszuführen, mit 0 params alle params, oder irgendeine # params.

InformationsquelleAutor der Antwort Eppz

Dies ist der Stil, den ich verwenden:

t-sql

SELECT    *        
FROM    table        
WHERE     
status_id    =    isnull(@status_id ,status_id)     
and    date    =    isnull(@date ,date )     
and    other_parameter    =    isnull(@other_parameter,other_parameter)

oracle

SELECT    *        
FROM    table        
WHERE     
status_id    =    nval(p_status_id ,status_id)     
and    date    =    nval(p_date ,date )     
and    other_parameter    =    nval(p_other_parameter,other_parameter)

InformationsquelleAutor der Antwort Rich

Können Sie etwas tun, wie

WHERE 
(
 ParameterA == 4 OR ParameterA IS NULL
)

AND
(
 ParameterB == 12 OR ParameterB IS NULL
)

InformationsquelleAutor der Antwort kemiller2002

2

Einer lesbaren und wartbaren Weg, es zu tun (auch verwendbar mit JOIN/ANWENDEN) :
```
where 
      (@parameter1 IS NULL OR your_condition1)
  and (@parameter2 IS NULL OR your_condition2) 
-- etc
```
Allerdings ist es eine schlechte Idee auf den meisten großen Tabellen (sogar noch mehr mit JOIN/ANWENDEN), da Ihre Ausführung planen, werden Sie nicht ignorieren NULL-Werte und erzeugt massiven performance-Lücke (ex : scaning alle einen Tisch suchen für NULL-Werte).

Einen Umweg in SQL Server ist für die Verwendung MIT(RECOMPILE) Optionen, die in der Abfrage (ab SQL 2008 SP1 CU5 (10.0.2746)).

Der beste Weg, um dies implementiert (performance-wise) ist die Verwendung von IF ... ELSE-block, eine für jede Kombination möglich. Vielleicht ist es anstrengend, aber Sie haben die besten Leistungen und ist es egal, Ihre Datenbank-Einstellungen.

Wenn Sie weitere Einzelheiten benötigen, können Sie sich für die KM. Antwort hier.

InformationsquelleAutor der Antwort Lostblue
1

Wenn Sie vermeiden wollen dynamisch den Aufbau von SQL-Zeichenfolgen (die oft am besten vermieden werden), können Sie dies in stored procs beim Vergleich der einzelnen Kriterien in Ihre wo claused mit einem default-Wert, das entspricht "ignore". E. g.:
```
select * from Table where
   (@Col1 IS NULL OR Col1 = @Col1) /*If you don't want to filter in @col, pass in NULL*/
   AND
   (@Col2 IS NULL OR Col2 = @Col2)
```
InformationsquelleAutor der Antwort JonoW

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.