articles

Articles of SQLschool.gr Team

Dynamic SQL ή μήπως μπορώ και αλλιώς;

Antonios Chatzipavlis

Saturday 19 February 2011

Σαν developers αρκετές φορές ερχόμαστε αντιμέτωποι με προβλήματα που πρέπει να λύσουμε μέσα σε σύντομο χρονικό διάστημα. Τις περισσότερες φορές η λύση που επιλέγουμε για να το λύσουμε είναι αυτή που ξέρουμε καλύτερα, την έχουν χρησιμοποιήσει αρκετές φορές, την εμπιστευόμαστε περισσότερο ή αυτή ξέρουμε μόνο.

Κάποιες φορές αυτή είναι ιδανική, καλή, μέτρια ή άστα να πάνε…

Ας έρθουμε όμως στο προκείμενο…

Πριν μερικές μέρες είχα να αντιμετωπίσω ένα θέμα το οποίο πάντα όταν ανακύπτει προκαλεί πολλά σχόλια, αρνητικά κυρίως, από συναδέλφους που βλέπουν το SQL Server σαν ένα μέσω αποθήκευσης και μόνο των δεδομένων, αυτό που εγώ λέω κουβά.

Το πρόβλημα είναι το εξής «Έχω ένα ή περισσότερους πίνακες που θέλω να τους ρωτήσω με πολλαπλά φίλτρα τα οποία άλλες φορές θα έχουν τιμή και άλλες όχι. Με ποιο απλά λόγια αν το φίλτρο έχει τιμή τότε θέλω να είναι στο WHERE αλλιώς όχι».

Αυτό όταν το ακούς ή το αντιμετωπίζεις, άμεσα το μυαλό σου πηγαίνει στο «θέλω δυναμικά να κτίζω το WHERE clause στο SELECT που θα κάνω».

Αυτό σημαίνει αρκετά πράγματα τα οποία θα ήταν κουτό από μέρος μου να κάτσω να τα γράψω μιας και ο Erland Sommarskog (blog), MVP και αυτός στον SQL Server, έχει ήδη γράψει ένα καταπληκτικό post με τίτλο «The Curse and Blessings of Dynamic SQL», το οποίο και εγώ πριν αρχίσω την συγγραφή αυτού του post δεν γνώριζα την ύπαρξη του. Αλλά ευτυχώς είχα την προνοητικότητα να ρωτήσω τους άλλους MVPs αν κάποιος έχει γράψει κάτι για αυτό ώστε να εστιάσω την προσοχή μου σε αυτό που θέλω να αναλύσω χωρίς να γράψω όλη την ιστορία από την αρχή.Thanks Er!.

Ας πάρουμε για παράδειγμα το εξής:

Θέλω να έχω μια stored procedure που να παίρνει σαν παραμέτρους πεδία του πίνακα Customers και του πίνακα Orders από την Northwind και ανάλογα να κάνει αναζήτηση με το τι τιμές ή όχι έχω δώσει σε αυτές και να μου επιστρέφει το επιθυμητό αποτέλεσμα που στην περίπτωση του παραδείγματος μας θα είναι οι παραγγελίες ανά πελάτη.

Αυτό σημαίνει ότι η συγκεκριμένη stored pocedure θα πρέπει να έχει την δυνατότητα να μην πάρει τιμές σε καμία από αυτές άρα θα πρέπει αυτές να είναι αρχικοποιημένες με μια τιμή που στην περίπτωση μας η τιμή αυτή θα είναι null. Έτσι ένα πιθανό stored procedure signature θα μπορούσε να είναι το παρακάτω

CREATE PROC spGetCustomerOrders 
                      @CompanyName NVARCHAR(40)=NULL, 
                      @Country NVARCHAR(15)=NULL, 
                      @City NVARCHAR(15)=NULL, 
                      @Region NVARCHAR(15)=NULL, 
                      @OrderDate_Min DATETIME=NULL, 
                      @OrderDate_Max DATETIME=NULL, 
                      @Employees NVARCHAR(100)=NULL

Το αποτέλεσμα που θέλω να επιστρέφει είναι το εξής query

SELECT o.OrderID, 
       o.EmployeeID, 
       c.CustomerID, 
       c.CompanyName, 
       o.OrderDate, 
       c.Country, 
       c.City, 
       c.Region 
FROM Customers c 
INNER JOIN Orders o ON c.CustomerID=o.CustomerID

το οποίο θέλω να έχει τα εξής φίλτρα κατά την αναζήτηση φυσικά όταν για αυτά μου έχουν δώσει τιμές αλλιώς δεν θέλω να μπλέκονται σε αυτή. Σε πλήρη ανάπτυξη τα φίλτρα μου θέλω να είναι όπως παρακάτω

WHERE 
c.CompanyName LIKE @CompanyName + '%' 
AND 
c.Country = @Country 
AND 
c.City = @City 
AND 
c.Region = @Region 
AND 
o.OrderDate BETWEEN @OrderDate_Min AND @OrderDate_Max 
AND 
o.EmployeeID IN (<Range>)

Αν σαν πρώτη σκέψη είχα το dynamic sql τότε μια πιθανή υλοποίηση θα ήταν η παρακάτω

CREATE PROC spGetCustomerOrders  @CompanyName NVARCHAR(40)=NULL, 
                                 @Country NVARCHAR(15)=NULL, 
                                 @City NVARCHAR(15)=NULL, 
                                 @Region NVARCHAR(15)=NULL, 
                                 @OrderDate_Min DATETIME=NULL, 
                                 @OrderDate_Max DATETIME=NULL, 
                                 @Employees NVARCHAR(100)=null 
AS 

SET NOCOUNT ON; 

DECLARE @select nvarchar(2000) 
DECLARE @where nvarchar(1000) 

SET @OrderDate_Min = ISNULL(@OrderDate_Min,'19000101') 
SET @OrderDate_Max = ISNULL(@OrderDate_Max,'99991231') 

IF ( @OrderDate_Min > @OrderDate_Max ) 
BEGIN 
   RAISERROR ('@OrderDate_Min is bigger than @OrderDate_Max',16,1) 
   RETURN 
END 

SET @select = 'SELECT o.OrderID, o.EmployeeID, c.CustomerID, c.CompanyName, o.OrderDate, c.Country, c.City, c.Region FROM Customers c INNER JOIN Orders o ON c.CustomerID=o.CustomerID ' 

-- ΓΙΑ ΝΑ ΕΙΜΑΙ ΑΣΦΑΛΕΙΣ ΕΔΩ ΘΑ ΠΡΈΠΕΙ ΝΑ ΕΛΕΞΩ ΓΙΑ SQL INJECTION 

SET @where = 'WHERE (o.OrderDate BETWEEN ''' + CONVERT(char(10),@OrderDate_Min,102) + ''' AND ''' + CONVERT(char(10),@OrderDate_Max,102) + ''')' 
if not @CompanyName is null SET @where += ' AND (c.CompanyName LIKE ''' + @CompanyName + '%' +''')' 
if not @Country is null SET @where += ' AND (c.Country = ''' + @Country + ''')' 
if not @City is null SET @where += ' AND (c.City = ''' + @City + ''')' 
if not @Region is null SET @where += ' AND (c.Region = ''' + @Region + ''')' 
if not @Employees is null SET @where += ' AND (o.EmployeeID IN ('+ @Employees + '))' 
SET @select += @where 
EXEC (@select) 
GO

Και ο τρόπος εκτέλεσης της θα ήταν ο παρακάτω

exec spGetCustomerOrders @CompanyName = 'c', 
                         @Country ='UK', 
                         @City =null, 
                         @Region =null, 
                         @OrderDate_Min =null, 
                         @OrderDate_Max =null, 
                         @Employees ='1,2,3,4'

Ουφ κουράστηκα να γράφω και να προσέχω τα μονά quotes που ανοίγουν που κλείνουν να βάζω ακόμα ένα σε αυτά ώστε να συμπεριληφθούν στο string, α και να προσέξω για sql injection.

Το τελευταίο θα μπορούσα εύκολα να το αποφύγω αν άλλαζα τον κτίσιμο του sql statement όπως παρακάτω

CREATE PROC spGetCustomerOrders  @CompanyName NVARCHAR(40)=NULL, 
                                 @Country NVARCHAR(15)=NULL, 
                                 @City NVARCHAR(15)=NULL, 
                                 @Region NVARCHAR(15)=NULL, 
                                 @OrderDate_Min DATETIME=NULL, 
                                 @OrderDate_Max DATETIME=NULL, 
                                 @Employees NVARCHAR(100)=null 
AS 

SET NOCOUNT ON; 

DECLARE @select nvarchar(2000) 
DECLARE @where nvarchar(1000) 

SET @OrderDate_Min = ISNULL(@OrderDate_Min,'19000101') 
SET @OrderDate_Max = ISNULL(@OrderDate_Max,'99991231') 

IF ( @OrderDate_Min > @OrderDate_Max ) 
BEGIN 
     RAISERROR ('@OrderDate_Min is bigger than @OrderDate_Max',16,1) 
     RETURN 
END 

SET @select = 'SELECT o.OrderID, o.EmployeeID, c.CustomerID, c.CompanyName, o.OrderDate, c.Country, c.City, c.Region FROM Customers c INNER JOIN Orders o ON c.CustomerID=o.CustomerID ' 
SET @where = 'WHERE (o.OrderDate BETWEEN @OrderDate_Min AND @OrderDate_Max)' 

if not @CompanyName is null SET @where += ' AND (c.CompanyName LIKE @CompanyName)' 
if not @Country is null SET @where += ' AND (c.Country = @Country)' 
if not @City is null SET @where += ' AND (c.City = @City )' 
if not @Region is null SET @where += ' AND (c.Region = @Region )' 
if not @Employees is null SET @where += ' AND (o.EmployeeID IN (@Employees))' 
SET @select += @where 

EXEC sp_executesql @select,N'@CompanyName NVARCHAR(40), @Country NVARCHAR(15), @City NVARCHAR(15), @Region NVARCHAR(15), @OrderDate_Min DATETIME, @OrderDate_Max DATETIME, @Employees NVARCHAR(100)', @CompanyName, @Country, @City, @Region, @OrderDate_Min, @OrderDate_Max, @Employees 

GO

Και ο τρόπος εκτέλεσης της θα ήταν ο παρακάτω

exec spGetCustomerOrders @CompanyName = 'c%', 
                         @Country ='UK', 
                         @City =null, 
                         @Region =null, 
                         @OrderDate_Min =null, 
                         @OrderDate_Max =null, 
                         @Employees =null

Μήπως όμως θα μπορούσα να την γράψω αλλιώς; Χωρίς dynamic sql; Ας το δοκιμάσουμε.

Αρχικά με ενοχλεί η παράμετρος @Employees που είναι nvarchar. Θα ήθελα να είναι array. Μα καλά θα μου πεις array σε T-SQL, τι πίνεις;

Όντως η έννοια αυτή δεν υπάρχει σε T-SQL, όμως υπάρχουν οι πίνακες και τα table-value parameters. Έτσι φτιάχνω ένα δικό μου data type που θα είναι table data type και θα παίζει το ρόλο του array

CREATE TYPE OrderEmployees AS TABLE (EmployeeID INT); 
GO

Και θα αλλάξω την παράμετρο @Employees σε αυτό το table data type. Έτσι πλέον το stored procedure signature θα γίνει

CREATE PROC spGetCustomerOrders  @CompanyName NVARCHAR(40)=NULL, 
                                 @Country NVARCHAR(15)=NULL, 
                                 @City NVARCHAR(15)=NULL, 
                                 @Region NVARCHAR(15)=NULL, 
                                 @OrderDate_Min DATETIME=NULL, 
                                 @OrderDate_Max DATETIME=NULL, 
                                 @Employees OrderEmployees READONLY

Για να ξέρω αν έχω rows στο @Employees table-value parameter δηλώνω την μεταβλητή @ HasEmployees σαν bit (Boolean) και την γεμίζω ανάλογα 0 δεν έχω, 1 έχω rows

DECLARE @HasEmployees BIT 
SET @HasEmployees = CASE WHEN (SELECT COUNT(*) FROM @Employees)>0 THEN 1 ELSE 0 END

Αρχικοποιώ και τις @OrderDate_Min, @OrderDate_Max ώστε να μπορώ να παίρνω όλα τα rows σε περίπτωση που δεν μου δώσουν τιμές

SET @OrderDate_Min = ISNULL(@OrderDate_Min,'19000101') 
SET @OrderDate_Max = ISNULL(@OrderDate_Max,'99991231')

Η υλοποίηση της θα μπορούσε να είναι η παρακάτω

CREATE PROC spGetCustomerOrders @CompanyName NVARCHAR(40)=NULL, 
                                @Country NVARCHAR(15)=NULL, 
                                @City NVARCHAR(15)=NULL, 
                                @Region NVARCHAR(15)=NULL, 
                                @OrderDate_Min DATETIME=NULL, 
                                @OrderDate_Max DATETIME=NULL, 
                                @Employees OrderEmployees READONLY 
AS 

SET NOCOUNT ON; 

DECLARE @HasEmployees BIT 
SET @OrderDate_Min = ISNULL(@OrderDate_Min,'19000101') 
SET @OrderDate_Max = ISNULL(@OrderDate_Max,'99991231') 
SET @HasEmployees = CASE WHEN (SELECT COUNT(*) FROM @Employees)>0 THEN 1 ELSE 0 END 

SELECT o.OrderID, o.EmployeeID, c.CustomerID,c.CompanyName,o.OrderDate,c.Country,c.City,c.Region 
FROM Customers c 
INNER JOIN Orders o ON c.CustomerID=o.CustomerID 
WHERE 
((c.CompanyName LIKE @CompanyName + '%') OR (@CompanyName IS NULL)) 
AND 
((c.Country = @Country) OR (@Country IS NULL)) 
AND 
((c.City = @City) OR (@City IS NULL)) 
AND 
((c.Region = @Region) OR (@Region IS NULL)) 
AND 
(o.OrderDate BETWEEN @OrderDate_Min AND @OrderDate_Max ) 
AND 
(CASE @HasEmployees 
WHEN 0 THEN 1 
ELSE 
     CASE WHEN o.EmployeeID IN (SELECT EmployeeID FROM @Employees) THEN 1 
     ELSE 0 
     END 
END = 1 ) 
GO

Και η εκτέλεση αυτής

DECLARE @e OrderEmployees 
-- Κάνω remark το insert αυτό αν θέλω να προσομοιάσω το γεγονός ότι δεν μου δίνουν τιμές στους υπαλλήλους 
INSERT INTO @e VALUES (8),(4) 

EXEC spGetCustomerOrders @CompanyName='c', 
                         @Country='UK', 
                         @City=NULL, 
                         @Region=NULL, 
                         @OrderDate_Min=NULL, 
                         @OrderDate_Max=NULL, 
                         @Employees=@e

Το σημαντικό όμως είναι η διαφορά σε χρόνο εκτέλεσης καθώς η λύση που δεν περιέχει το dynamic sql είναι γρηγορότερη τουλάχιστον κατά 60%

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.