Updating table based on Select Query in StoredProc / ColdFusion

Question

I am using ColdFusion for for a project and I have a written a query which I think can be faster using a Stored Proc, but I not a T-SQL person, so I am not sure how to do it to compare.

I am running an initial query which selects a number of fields from a table based on a dynamically built cfquery. I think I know how to convert this query into the MS SQL store proc. However, directly after that, I then take all of the primary key IDs from that query and run another query against a separate table that "locks" records with those IDs. The lock is a bit field (a flag) in the second table that tells the system that this record is "checked out". I have wrapped both queries in a cftransaction so that they execute as a unit.

Code Overview:

<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>

I then return the selectQuery resultset to my app which uses it for outputing some data. How would I accomplish the same thing in a single MS SQL 2008 StoredProc that I could call using cfstoredproc? Again, I am thinking that the native CF way (with cfquery) is not a efficient as a stored proc since I have to retrieve the resultset back to CF, then call another query back to the DB. A single stored proc does everything in the DB and then returns the original query resultset for use.

Any ideas?

Answer 1

You could do your update in one query by making your first query a subquery and then using a separate statement to return your results. The whole thing could be a single stored procedure:

  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

You'll probably have to pass some parameters in, but that's the basic structure of a stored procedure. Then you can call it from ColdFusion like so:

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

You could use those query results just like you were using them before.

Answer 2

your problem is "bunch of conditions here" are those conditions always static? So is it ALWAYS: (FOO = @x AND BAR = @y)? Or is it conditional where sometimes FOO does not exist at all as a condition?

If FOO is not always present then you have a problem with the stored proc. T-SQL cannot do dynamic query building, in fact even allowing it would kind of negate the point of the proc, which is to compile and pre-optomize the SQL. You CAN do it of course, but you end up just having to build a SQL string inside the proc body and then executing it at the end. your much better off using CFQuery with cfqueryparams. Actually have you considered doig this:

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

Answer 3

You could add an OUTPUT clause to the UPDATE statement to capture the id's of the records updated and insert them into a table variable/temp table. Then JOIN back to table1 to return the result set.

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

Keep in mind that if table1 is in constant flux, the other values ("field2" and "field3") are not guaranteed to be what they were when the UPDATE occurred. But I think your current method is susceptible to that issue as well.