Aktualisierungs-Tabelle basierend auf Select-Abfrage in gespeicherter Prozedur / ColdFusion

Ich benutze ColdFusion für ein Projekt und ich habe eine schriftliche Abfrage, die ich denke, kann schneller mit einer gespeicherten Prozedur, aber ich nicht eine T-SQL Person, so bin ich nicht sicher, wie man es zu vergleichen.

Ich laufe eine erste Abfrage, die eine Anzahl von Feldern aus einer Tabelle basierend auf einer dynamisch aufgebauten Cfquery auswählt. Ich glaube, ich weiß, wie man diese Abfrage in die SQL server gespeicherte Prozedur umwandelt.

Doch direkt danach, dann nehme ich dann alle Primärschlüssel-IDs aus dieser Abfrage und laufe eine andere Abfrage gegen eine separate Tabelle, die "datasätze mit diesen IDs" sperrt. Die Sperre ist ein Bit-Feld (ein Flag) in der zweiten Tabelle, die dem System mitteilt, dass dieser datasatz "ausgecheckt" ist. Ich habe beide Abfragen in eine cftransaction so dass sie als Einheit ausführen.

Codeübersicht:

 <cftransaction> <cfquery name="selectQuery"> SELECT id, field2, field3 FROM table1 WHERE (bunch of conditions here) </cfquery> <cfquery name="updateQuery"> UPDATE table2 SET lockField = 1 WHERE table2.id IN (#ValueList(selectQuery.id#) </cfquery> </cftransaction> 

Ich gebe dann das selectQuery Resultset zu meiner App zurück, die es für die Ausgabe einiger data verwendet. Wie würde ich das Gleiche in einer einzigen SQL server 2008 gespeicherte Prozedur erreichen, die ich mit cfstoredproc anrufen cfstoredproc ?

Auch hier denke ich, dass der native CF-path (mit cfquery ) nicht so effizient ist wie eine gespeicherte Prozedur, da ich das Resultset wieder auf CF zurückholen muss, dann rufe eine andere Abfrage an die DB zurück. Eine einzelne gespeicherte Prozedur macht alles in der DB und gibt dann das ursprüngliche Abfrageergebnis zurück.

Irgendwelche Ideen?

   

Sie können der UPDATE-statement eine OUTPUT-Klausel hinzufügen, um die IDs der Aufzeichnungen zu erfassen und sie in eine Tabellenvariable / temp-Tabelle einzufügen. Dann kehren Sie zurück zu table1, um die Ergebnismenge zurückzusenden.

 DECLARE @UpdatedRecords TABLE ( ID INT ) UPDATE t2 SET t2.lockField = 1 OUTPUT Inserted.ID INTO @UpdatedRecords ( ID ) FROM table2 t2 INNER JOIN table1 t1 ON t2.id = t1.id WHERE (bunch of conditions for table1 here) SELECT t1.id, t1.field2, t1.field3 FROM table1 t1 INNER JOIN @UpdatedRecords u ON t1.id = u.id 

Denken Sie daran, dass, wenn table1 in ständigem Fluss ist, die anderen Werte ("field2" und "field3") nicht garantiert sind, was sie waren, als das UPDATE auftrat. Aber ich denke, Ihre aktuelle Methode ist anfällig für diese Frage auch.

Ihr Problem ist "Haufen von Bedingungen hier". Sind diese Bedingungen immer static? So ist es IMMER: (FOO = @x UND BAR = @y)? Oder ist es bedingt, wo manchmal FOO überhaupt nicht als Bedingung existiert?

Wenn FOO nicht immer vorhanden ist, dann hast du ein Problem mit dem gespeicherten Proc. T-SQL kann nicht dynamische Abfrage Gebäude, in der Tat sogar erlauben es würde irgendwie negieren den Punkt der proc, die zu kompilieren und vor-optimieren die SQL. Sie können es natürlich tun, aber Sie am Ende nur mit einem SQL-String in der Proc-Körper zu build und dann ausführen sie am Ende. Du bist viel besser mit CFQuery mit cfqueryparams. Tatsächlich hast du das gedacht?

  <cfquery name="updateQuery"> UPDATE table2 SET lockField = 1 WHERE table2.id IN (SELECT id FROM table1 WHERE (bunch of conditions here)) </cfquery> 

Sie können Ihr Update in einer Abfrage durchführen, indem Sie Ihre erste Abfrage eine Unterabfrage und dann mit einer separaten statement, um Ihre Ergebnisse zurückzugeben. Die ganze Sache könnte eine einzige gespeicherte Prozedur sein:

  CREATE PROCEDURE myUpdate @Variable [datatype], etc... AS BEGIN UPDATE table2 SET lockField = 1 WHERE table2.id IN ( SELECT id FROM table1 WHERE (bunch of conditions here) ) SELECT id, field2, field3 FROM table1 WHERE (bunch of conditions here) END 

Du musst wahrscheinlich einige Parameter passieren, aber das ist die Grundstruktur einer gespeicherten Prozedur. Dann kannst du es von ColdFusion so nennen:

 <cfstoredproc procedure="myUpdate"> <cfprocparam type="[CF SQL Type]" value="[CF Variable]"> etc... <cfprocresult name="selectQuery" resultSet="1"> </cfstoredproc> 

Du könntest diese Abfrageergebnisse verwenden, so wie du sie vorher benutzt hast.

Keine Notwendigkeit für einen SPROC.

 UPDATE table2 SET table2.lockField = 1 FROM table1 WHERE table1.id = table2.id AND table1.field2 = <cfqueryparam ....> AND table1.field3 = <cfqueryparam ....>