CASE WHEN NULL

lunedì 12 dicembre 2005 - 18.00

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

Klaus-zanini Profilo | Senior Member

364 messaggi | Data Invio: lun 12 dic 2005 - 18:00

Ciao a tutti,
sto eseguendo un stored procedure che nella propria condizione where contiene dei parametri che a seconda del loro valore mi dovrebbero cambiare la condizione.
In particolare io vorrei che il mio parametro intero @Autore se vale -1 faccia non applichi il filtro quindi

Where CodiceAutore=CASE @Autore WHEN -1 THEN CodiceAutore ELSE @Autore END

Con questo tutto funziona perché se l'autore è -1 si verifica la condizione CodiceAutore=CodiceAutore che è sempre vera e quindi visualizzo tutti i record...o quasi!!!! >o(
Infatti il problema è che non vengono recuperati i record con valore NULL in CodiceAutore....

Qualche suggerimento?

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: lun 12 dic 2005 - 21:34

> In particolare io vorrei che il mio parametro intero @Autore se vale -1
> faccia non applichi il filtro quindi
>
> Where CodiceAutore=CASE @Autore WHEN -1 THEN CodiceAutore ELSE @Autore END
>
> Con questo tutto funziona perché se l'autore è -1 si verifica la condizione
> CodiceAutore=CodiceAutore che è sempre vera e quindi visualizzo tutti i record...o quasi!!!! >o(
> Infatti il problema è che non vengono recuperati i record con valore NULL in CodiceAutore....

Ciao Klaus-zanini,

se proprio vuoi scrivere una stored procedure parametrizzabile dinamicamente, ti suggerisco di ricorrere al dynamic SQL che oltre a garantirti query plan migliori rispetto alla soluzione da te adottata, risolve anche il problema che segnali.

Guarda il seguente esempio proposto dal SQL Server MVP Itzik Ben-Gan:

USE tempdb;
GO

IF OBJECT_ID('dbo.usp_GetOrders') IS NOT NULL
DROP PROC dbo.usp_GetOrders;
IF OBJECT_ID('dbo.Orders') IS NOT NULL
DROP TABLE dbo.Orders;
GO

SELECT OrderID, CustomerID, EmployeeID, OrderDate
INTO dbo.Orders
FROM Northwind.dbo.Orders;

CREATE CLUSTERED INDEX idx_cl_OrderDate ON dbo.Orders(OrderDate);
CREATE UNIQUE INDEX idx_unc_OrderID ON dbo.Orders(OrderID);
CREATE INDEX idx_nc_CustomerID ON dbo.Orders(CustomerID);
CREATE INDEX idx_nc_EmployeeID ON dbo.Orders(EmployeeID);
GO

CREATE PROC dbo.usp_GetOrders
@OrderID AS INT = NULL,
@CustomerID AS NCHAR(5) = NULL,
@EmployeeID AS INT = NULL,
@OrderDate AS DATETIME = NULL
AS

DECLARE @sql AS NVARCHAR(4000);

SET @sql =
N'SELECT OrderID, CustomerID, EmployeeID, OrderDate'
+ N' FROM dbo.Orders'
+ N' WHERE 1 = 1'
+ CASE WHEN @OrderID IS NOT NULL THEN
N' AND OrderID = @oid' ELSE N'' END
+ CASE WHEN @CustomerID IS NOT NULL THEN
N' AND CustomerID = @cid' ELSE N'' END
+ CASE WHEN @EmployeeID IS NOT NULL THEN
N' AND EmployeeID = @eid' ELSE N'' END
+ CASE WHEN @OrderDate IS NOT NULL THEN
N' AND OrderDate = @dt' ELSE N'' END;

EXEC sp_executesql
@sql,
N'@oid AS INT, @cid AS NCHAR(5), @eid AS INT, @dt AS DATETIME',
@oid = @OrderID,
@cid = @CustomerID,
@eid = @EmployeeID,
@dt = @OrderDate;
GO

EXEC dbo.usp_GetOrders @OrderDate = '19970101';
GO

/* Output:

OrderID CustomerID EmployeeID OrderDate
----------- ---------- ----------- -----------------------
10400 EASTC 1 1997-01-01 00:00:00.000
10401 RATTC 1 1997-01-01 00:00:00.000

(2 row(s) affected)

*/

EXEC dbo.usp_GetOrders @OrderDate = '19970102';
GO

/* Output:

OrderID CustomerID EmployeeID OrderDate
----------- ---------- ----------- -----------------------
10402 ERNSH 8 1997-01-02 00:00:00.000

(1 row(s) affected)

*/

EXEC dbo.usp_GetOrders @OrderID = 10248;
GO

/* Output:

OrderID CustomerID EmployeeID OrderDate
----------- ---------- ----------- -----------------------
10248 VINET 5 1996-07-04 00:00:00.000

(1 row(s) affected)

*/

EXEC dbo.usp_GetOrders @OrderID = 10249;
GO

/* Output:

OrderID CustomerID EmployeeID OrderDate
----------- ---------- ----------- -----------------------
10249 TOMSP 6 1996-07-05 00:00:00.000

(1 row(s) affected)

*/

EXEC dbo.usp_GetOrders @OrderDate = '19960801', @EmployeeID = 1
GO

/* Output:

OrderID CustomerID EmployeeID OrderDate
----------- ---------- ----------- -----------------------
10270 WARTH 1 1996-08-01 00:00:00.000

(1 row(s) affected)

*/

(la spiegazione di questo esempio te la posto in un nuovo post)

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: lun 12 dic 2005 - 21:35

(parte 2)

La stored procedure costruisce dinamicamente la query string memorizzandola nella variabile locale @sql. Per ogni argomento di input non nullo, il codice concatena un filtro alla clausola WHERE.

La prima espressione logica che appare nella clausola WHERE è 1 = 1. Questo trucchetto ti evita di stabilire di volta in volta se occorre specificare la clausola WHERE oppure se occorre aggiungere l'operatore logico AND ad un filtro.
Il Query Optimizer semplicemente ignorerà l'espressione 1 = 1 in quanto non ha effetto sulla query.

Per invocare la query costruita dinamicamente viene utilizzata la stored procedure di sistema sp_executesql. Dato che il codice contiene solamente i filtri rilevanti senza manipolazioni complesse, il query plan generato sarà estremamente efficiente.

Infine l'utilizzo della stored procedure di sistema sp_executesql farà in modo di creare 1 solo query plan per ogni lista distinta di argomenti, indipendentemente dal loro valore.

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

Klaus-zanini Profilo | Senior Member

364 messaggi | Data Invio: mar 13 dic 2005 - 09:36

Grazie del consiglio,
nel frattempo un mio collega mi ha fatto notare che posso utilizzare la funzione ISNULL(Autore,0) che in caso di valore NULL del mio campo va a sostituirci il valore 0 permettendo recuperando così anche quel record.

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: mar 13 dic 2005 - 11:58

>nel frattempo un mio collega mi ha fatto notare che posso utilizzare
>la funzione ISNULL(Autore,0) che in caso di valore NULL del
>mio campo va a sostituirci il valore 0 permettendo recuperando
> così anche quel record.

Si, ma confronta i piani di esecuzioni del tuo codice rispetto al mio (o meglio a quello di Itzik) e ti renderai conto di quanto sia inefficiente.

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

Torna su

Stanze Forum

Elenco Threads

Partecipa anche tu! Registrati!

Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !