Dynamische Where-Parameter ohne dynamic SQL

Dynamische Where-Parameter ohne dynamic SQL

Das verwenden von dynamic SQL in gespeicherten Prozeduren sollte aus Sicherheitsgründen vermieden werden (Stichwort SQL injection, d.h. das Einschleusen von schädlichen Statements).
Auch ist es schwerer lesbar als SQL statements, die direkt im Editor stehen.

Ein Beispiel für dynamic SQL ist:

declare @sql nvarchar(max)
set @sql = 'select * from Artikel where Status = 1'

EXEC sp_executesql @sql

Ohne dynamic SQL steht das statement z.B. am Ende einer gespeicherten Prozedur direkt ohne Anführungszeichen:

select * from Artikel where Status=1

Häufig wird dynamic SQL verwendet, um damit SQL statements zusammenzusetzen, insbesondere where-Bedingungen anzuhängen, die z.B. von übergebenen Parametern abhängen:

CREATE PROCEDURE [dbo].[spDynamicSQL]
  @status int
AS
BEGIN
  SET NOCOUNT ON;

  declare @sql nvarchar(max)
  set @sql = 'select * from Artikel where Status = ' + cast(@status as varchar(5))

  EXEC sp_executesql @sql
END

Das stellt noch kein Problem dar und lässt sich leicht als ‚direct SQL‘ darstellen:

select * from Artikel where Status=@status

Problematischer wird es, wenn der Parameter optional ist. Die dynamic Variante wäre:

ALTER PROCEDURE spDynamicSQL
  @status int=NULL
AS
BEGIN
  SET NOCOUNT ON;

  declare @sql nvarchar(max)
  set @sql = 'select * from Artikel'

  if not @status is null
    set @sql = @sql + ' where Status = ' + cast(@status as varchar(5))

  EXEC sp_executesql @sql

END

Das lässt sich dann aufrufen mit dem Parameter ’status‘:

exec spDynamicSQL 1

oder ohne:

exec spDynamicSQL

Auch dies ist ohne dynamic SQL erreichbar:

CREATE PROCEDURE [dbo].[spDirectSQL] 
    @status int=NULL
AS
BEGIN
    SET NOCOUNT ON;
    select * from Artikel where Status = @status or @status is NULL
END

Erläuterung:

Wenn @status nicht übergeben wird (alle Artikel sollen angezeigt werden), dann wird @status innerhalb der Prozedur auf NULL gesetzt. Im sql statement liefert dann der Teil „Status = @status“ bai allen Datensätzen FALSCH und der Teil „@status is NULL“ liefert immer WAHR -> Die Folge ist, dass alle Artikel zurückgegeben werden.

Wenn @status=1 übergeben wird (nur Artikel mit Status=1 sollen angezeigt werden), dann liefert der Teil „Status = @status“ bai allen Datensätzen mit Status=1 WAHR und der Teil „@status is NULL“ liefert immer FALSCH-> Es werden alle Artikel zurückgegeben, bei denen Status = 1 gilt

Etwas komplizierter wird es, wenn auch auf den Status NULL abgefragt werden soll:

CREATE PROCEDURE [dbo].[spDirectSQL]
  @status int=-1
AS
BEGIN
  SET NOCOUNT ON;
  select * from Artikel where (Status is null and @status is null) or (Status = @status or @status= -1)
  OPTION (RECOMPILE)
END

Erläuterung:

Als Default-Wert (hier: -1) muss ein Wert angegeben werden, der nicht als Feldwert vorkommt (mindestens: auf den mit der Prozedur nicht abgefragt werden soll).
Wenn nun der Wert NULL an die Prozedur übergeben wird, dann liefert der hintere Teil „(Status = @status or @status= -1)“ immer FALSCH, der vordere Teil „(Status is null and @status is null)“ liefert aber genau dann WAHR, wenn Status NULL ist.
Bei Übergabe keines oder eines anderen Parameters liefert „(Status is null and @status is null)“ immer FALSCH und der hintere Teil „(Status = @status or @status= -1)“ funktioniert wie oben, so dass insgesamt alle 3 Aufrufe die richtigen Ergebnisse liefert:

exec spDirectSQL NULL -- liefert Artikel mit Status = NULL
exec spDirectSQL 1    -- liefert Artikel mit Status = 1
exec spDirectSQL      -- liefert alle Artikel

Für diese Art von dynamischen sql statments wir das Setzen der Option OPTION (RECOMPILE) empfohlen. Dies veranlasst den SQL-Server, den Ausführungsplan jedes Mal aufs neue zu erstellen und nicht einmalig, beim ersten Aufruf. Dies ist natürlich sinnvoll, da ja in der Regel bei jedem Aufruf andere statements ausgeführt werden.

Auf diese Weise können auch Prozeduren mit mehreren Parametern erstellt werden. Die Where-Teile wie „Status = @status or @status is NULL“ müssen dann lediglich mit AND aneinander gehängt werden.