SQL Server (και άλλες databases)

Απ: Δυναμικό query σε stored procedure

DeClen — Fri, 04 May 2007 22:28:17 GMT

Παιδιά ευχαριστώ για τις κατατοπιστικότατες απαντήσεις.

Απ: Δυναμικό query σε stored procedure

Παναγιώτης Καναβός — Thu, 03 May 2007 23:39:38 GMT

agmarios:

ALTER PROCEDURE dbo.RetriveHistoryData
(
@histCustomerName nvarchar(50) = ''
)
AS

    SELECT hist_id, hist_cust_name
    FROM dbo.Connection_View_History
    WHERE hist_cust_name=@histCustomerName

έτσι είναι καλύτερα;

Έτσι δεν φέρνει το επιθυμητό αποτέλεσμα. Αν κάποιος δώσει τιμή NULL ή κενό string, το stored procedure θα ψάξει για εγγραφές με το κενό string, δεν θα τις επιστρέψει όλες.

Απ: Δυναμικό query σε stored procedure

KelMan — Thu, 03 May 2007 23:03:25 GMT

Ορίστε σε μορφή Stored Procedure αυτό που σας έλεγα νωρίτερα και βασίζεται στην τεχνική του Σωτήρη:

/****** Object: Stored Procedure dbo.pr_InvoicesView_SelectAllWLogicAndFilters Script Date: 15/6/2005 1:41:47 ìì ******/

CREATE PROCEDURE [dbo].[pr_InvoicesView_SelectAllWLogicAndFilters]

@SerialNo varchar(50),

@opSerialNo varchar(11),

@DateFrom datetime,

@opDateFrom varchar(11),

@DateTo datetime,

@opDateTo varchar(11),

@Amount decimal(12, 2),

@opAmount varchar(11),

@PayedUp bit,

@opPayedUp varchar(11),

@iErrorCode int OUTPUT

SELECT [InvoiceID],

[CustomerID],

[SerialNo],

[DocumentTypeID],

[InvoiceTypeDescription],

[IssueDate],

[InterestEffectiveDate],

[DueDays],

[GracePeriod],

[Amount],

[PayedUp],

[InterestRate],

[PaymentID],

[PaymentSerialNo]

FROM [dbo].[InvoicesView]

WHERE CASE @opSerialNo

WHEN 'all' THEN 1

WHEN 'equal' THEN CASE WHEN [SerialNo] LIKE @SerialNo THEN 1

ELSE 0

END

WHEN 'beginswith' THEN CASE WHEN [SerialNo] LIKE @SerialNo + '%' THEN 1

ELSE 0

END

WHEN 'endswith' THEN CASE WHEN [SerialNo] LIKE '%' + @SerialNo THEN 1

ELSE 0

END

WHEN 'contains' THEN CASE WHEN [SerialNo] LIKE '%' + @SerialNo + '%' THEN 1

ELSE 0

END

WHEN 'notequal' THEN CASE WHEN [SerialNo] NOT LIKE @SerialNo THEN 1

ELSE 0

END

END = 1

AND CASE @opDateFrom

WHEN 'all' THEN 1

WHEN 'equal' THEN CASE WHEN [IssueDate] = @DateFrom THEN 1

ELSE 0

END

WHEN 'after' THEN CASE WHEN [IssueDate] > @DateFrom THEN 1

ELSE 0

END

WHEN 'before' THEN CASE WHEN [IssueDate] < @DateFrom THEN 1

ELSE 0

END

WHEN 'notequal' THEN CASE WHEN [IssueDate] <> @DateFrom THEN 1

ELSE 0

END

END = 1

AND CASE @opDateTo

WHEN 'all' THEN 1

WHEN 'equal' THEN CASE WHEN [IssueDate] = @DateTo THEN 1

ELSE 0

END

WHEN 'after' THEN CASE WHEN [IssueDate] > @DateTo THEN 1

ELSE 0

END

WHEN 'before' THEN CASE WHEN [IssueDate] < @DateTo THEN 1

ELSE 0

END

WHEN 'notequal' THEN CASE WHEN [IssueDate] <> @DateTo THEN 1

ELSE 0

END

END = 1

AND CASE @opAmount

WHEN 'all' THEN 1

WHEN 'equal' THEN CASE WHEN [Amount] = @Amount THEN 1

ELSE 0

END

WHEN 'greaterthan' THEN CASE WHEN [Amount] > @Amount THEN 1

ELSE 0

END

WHEN 'lowerthan' THEN CASE WHEN [Amount] < @Amount THEN 1

ELSE 0

END

WHEN 'notequal' THEN CASE WHEN [Amount] <> @Amount THEN 1

ELSE 0

END

END = 1

AND CASE @opPayedUp

WHEN 'all' THEN 1

WHEN 'true' THEN CASE WHEN [PayedUp] = 1 THEN 1

ELSE 0

END

WHEN 'false' THEN CASE WHEN [PayedUp] = 0 THEN 1

ELSE 0

END

END = 1

-- Get the Error Code for the statement just executed.

SELECT @iErrorCode = @@ERROR

Απ: Δυναμικό query σε stored procedure

agmarios — Thu, 03 May 2007 22:18:05 GMT

ALTER PROCEDURE dbo.RetriveHistoryData
(
@histCustomerName nvarchar(50) = ''
)
AS

    SELECT hist_id, hist_cust_name
    FROM dbo.Connection_View_History
    WHERE hist_cust_name=@histCustomerName

έτσι είναι καλύτερα;

Απ: Δυναμικό query σε stored procedure

Παναγιώτης Καναβός — Thu, 03 May 2007 22:15:32 GMT

agmarios:
γιατί να μη βάλουμε ένα if στο procedure; όταν έχουμε πολλές παραμέτρους φτιάχνουμε μια sp για κάθε παράμετρο (ή τις ομαδοποιούμε όταν έχει νόημα π.χ. χώρα,νομός,δήμος)

Το αποτέλεσμα θα είναι το ίδιο. Το execution plan που θα δημιουργηθεί για το stored procedure θα είναι αυτό της πρώτης εκτέλεσης.

Απ: Δυναμικό query σε stored procedure

Παναγιώτης Καναβός — Thu, 03 May 2007 22:13:46 GMT

Το πρόβλημα είναι ότι το execution plan που θα δημιουργηθεί την πρώτη φορά που θα τρέξεις το stored procedure σχεδόν σίγουρα θα είναι λάθος για τα επόμενα. Βασικά, έχεις δύο επιλογές:

Να ξαναδημιουργείς το execution plan κάθε φορά που εκτελείται το stored procedure. Αυτό το πετυχαίνεις βάζοντας το WITH RECOMPILE στον ορισμό του stored procedure ή το RECOMPILE hint στο Select. Αν τα search queries εκτελούνται σπάνια (π.χ. 1 φορά το λεπτό) το κόστος του νέου execution plan δεν σε απασχολεί. Με αυτό τον τρόπο κρατάς και τον κώδικα σου καθαρό, και το sql μαζεμένο
Να δημιουργείς ένα καινούριο parameterized query κάθε φορά. Άν κάποιοι συνδυασμοί παραμέτρων εμφανίζονται πιο συχνά από κάποιους άλλους, θα γλυτώσεις τη συνεχή δημιουργία νέων execution plan. Αν όμως η κατανομή είναι σχετικά τυχαία, θα έχεις στη μνήμη μερικές δεκάδες execution plans χωρίς όφελος. Άσε που θα πρέπει να βάλεις τα queries μέσα στον κώδικα σου.

Τελικά, εξαρτάται από το τί συμπεριφορά περιμένεις να έχουν οι χρήστες σου. Επειδή τα search queries είναι σχετικά σπάνια, πιστεύω ότι η πρώτη λύση είναι αρκετά καλή.

Απ: Δυναμικό query σε stored procedure

agmarios — Thu, 03 May 2007 22:06:21 GMT

γιατί να μη βάλουμε ένα if στο procedure; όταν έχουμε πολλές παραμέτρους φτιάχνουμε μια sp για κάθε παράμετρο (ή τις ομαδοποιούμε όταν έχει νόημα π.χ. χώρα,νομός,δήμος)

ALTER PROCEDURE dbo.RetriveHistoryData
(
@histCustomerName nvarchar(50) = ''
)
AS
if (LEN(@histCustomerName) > 0) begin
    SELECT hist_id, hist_cust_name
    FROM dbo.Connection_View_History
    WHERE hist_cust_name=@histCustomerName
end
else begin
    SELECT hist_id, hist_cust_name
    FROM dbo.Connection_View_History
end

Απ: Δυναμικό query σε stored procedure

KelMan — Thu, 03 May 2007 21:50:51 GMT

Χρειάζεσαι αυτό: http://www.dotnetzone.gr/cs/blogs/sfilip/archive/2005/08/16/4302.aspx

Το έχω χρησιμοποιήσει ακριβώς γι αυτόν τον σκοπό που περιγράφεις και δουλεύει άψογα! Ως προς το performace σε επίπεδο βάσης, όταν φτάσει η ώρα μπορείς να κάνεις διάφορα κολπάκια (ειδικά στον SQL Server 2005) για να βελτιώσεις την κατάσταση.

Απ: Δυναμικό query σε stored procedure

DeClen — Thu, 03 May 2007 21:40:27 GMT

Παιδιά έχετε δίκιο.

Πραγματικά, χρησιμοποίησα την σύνταξη της Νατάσας και έτρεξε πολύ όμορφα. Αυτό που θέλω να υλοποιήσω είναι μια σελίδα "δυναμικής αναζήτησης". Παναγιώτη έχεις δίκιο ότι οι μεταβλητές είναι πολλές (12!) οπότε από ότι κατάλαβα οι stored procedures δεν θα με βοηθήσουν σε αυτόν τον τομέα.

Στην συγκεκριμένη περίπτωση τα dynamic queries είναι πιο γρήγορη λύση από τα "μεταβαλλόμενα" execution plans;

Απ: Δυναμικό query σε stored procedure

Παναγιώτης Καναβός — Thu, 03 May 2007 21:29:00 GMT

Η σύνταξη που σου δίνει η Νατάσα είναι σωστή. Έτσι όπως είναι γραμμένο το where, αν η παράμετρος δεν είναι NULL θα σου επιστρέψει μόνο τις εγγραφές που ταιριάζουν. Διαφορετικά, θα τις επιστρέψει όλες.

Το μόνο "περίεργο" με αυτό το where είναι ότι το execution plan που θα δημιουργηθεί δεν θα είναι και το καλύτερο. Βασικά, την πρώτη φορά που θα τρέξει το stored procedure θα δημιουργηθεί ένα execution plan για την τιμή της παραμέτρου. Αν η παράμετρος είναι Null, θα δημιουργηθεί ένα execution plan το οποίο θα είναι γρήγορο για να επιστρέψει όλες τις γραμμές. Αν υπάρχει τιμή, θα δημιουργηθεί ένα execution plan κατάλληλο για search και αργό για να επιστρέψει όλες τις γραμμές.

Μία λύση σε αυτό το πρόβλημα είναι να έχεις δύο διαφορετικά stored procedures τα οποία θα επιστρέφουν είτε συγκεκριμμένες, είτε όλες τις τιμές. Μετά, στην RetrieveHistoryData κάνεις τον έλεγχο της παραμέτρου και επιλέγεις την κατάλληλη stored procedure.

Αν έχεις πολλές παραμέτρους, η λύση αυτή δεν δουλεύει. Για 3 παραμέτρους θα χρειαζόσουν 9 stored procedures για να καλύψεις όλους τους συνδυασμούς! Σε αυτή την περίπτωση η χρήση dynamic sql είναι ευκολότερη αλλά θέλει πολύ προσοχή. Καλύτερα να φτιάξεις ένα parameterized query στον client παρά να φτιάξεις και να εκτελέσεις ένα χύμα string. Αλλά και στο stored procedure το ίδιο, καλύτερα να φτιάξεις ένα string το οποίο θα λαμβάνει παραμέτρους και να το εκτελεσεις με sq_executesql περνώντας τις παραμέτρους.

Παρεμπιπτόντως, η περίπτωση των πολλών μεταβλητών είναι μία από τις ΕΛΑΧΙΣΤΕΣ περιπτώσεις όπου έχει νόημα να χρησιμοποιήσεις dynamic sql

Απ: Δυναμικό query σε stored procedure

DeClen — Thu, 03 May 2007 21:14:34 GMT

Αρχικά thanks για την απάντηση,

Αν για παράδειγμα εκτελέσω την procedure με

EXEC dbo.RetriveHistoryData 'Kostas'

σύμφωνα με την σύνταξη που μου προτείνεις δεν θα μου επιστραφούν απο τι βάση οι τιμές 'Kostas' και NULL;

Από την άλλη αν περάσω NULL δεν θα μου επιστραφούν μόνο οι NULL (..ενώ θα ήθελα να επιστραφούν όλα);

Σημείωση: διόρθωσα την απάντησή μου...

Απ: Δυναμικό query σε stored procedure

Νατάσα Μανουσοπούλου — Thu, 03 May 2007 21:00:49 GMT

Θα μπορούσες να τη γράψεις έτσι:

ALTER PROCEDURE dbo.RetriveHistoryData
(
@histCustomerName nvarchar(50)
)
AS
SELECT hist_id, hist_cust_name FROM dbo.Connection_View_History
WHERE
hist_cust_name=@histCustomerName OR
@histCustomerName IS NULL

και να περνάς NULL όταν τα θέλεις όλα. Έτσι γλιτώνεις και τη δυναμική SQL και το security hole του string concatenation.

Δυναμικό query σε stored procedure

DeClen — Thu, 03 May 2007 20:51:45 GMT

Χαίρετε,

Προσπαθώ να κάνω πάνω-κάτω αυτό που περιγράφει ο τίτλος. Θέλω να φτιάξω μια stored procedure (SQL Server Express) η οποία θα εκτελεί ένα δυναμικό query. Πιο συγκεκριμένα έχω φτιάξει το παρακάτω:

ALTER PROCEDURE dbo.RetriveHistoryData
(
  @histCustomerName nvarchar(50)
)
AS

  --DECLARING VARIABLES
  DECLARE @mySQLStatement nvarchar(255),
   @mySQLParameters nvarchar(255)

  --STABLE PART
  SET @mySQLStatement="SELECT hist_id, hist_cust_name  FROM dbo.Connection_View_History"

  --DYNAMIC PART
  CASE @histCustomerName
  WHEN '-no-data-' THEN SET @mySQLParameters=""
  ELSE SET @mySQLParameters="WHERE hist_cust_name = '" + @histCustomerName + "'"
  END

  --EXECUTE
  EXEC (@mySQLStatement + @mySQLParameters)

Αυτό που προσπαθώ να κάνω είναι αν η μεταβλητή @histCustomerName πάρει την τιμή "-no-data-" να μην εκτελεστεί το WHERE. Σε οποιαδήποτε άλλη περίσπτωση θέλω το WHERE να εκτελεστεί. Η παραπάνω (πολύ-θα-ήθελα-να-είμαι) stored procedure από το case και μετά μου πετάει λάθη.

Μπορεί κάποιος να βοηθήσει; (η αλήθεια είναι οπως με τις stored procedures δεν τα παω και πολύ καλά...)

Ευχαριστώ.