StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdating table based on Select query in stored procedure / ColdFusion
primarykey
Id
1332310
data
AcceptedAnswerId
0
AnswerCount
4
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2009-08-26T04:27:00.753
FavoriteCount
0
LastActivityDate
2016-12-24T10:24:17.837
LastEditDate
2016-12-24T10:24:17.837
LastEditorUserId
13302
OwnerUserId
159828
ParentId
0
PostTypeId
1
Score
0
ViewCount
1939
LastEditorDisplayName
text
Body
I am using ColdFusion for for a project and I have a written a query which I think can be faster using a stored procedure, 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 SQL Server stored procedure. 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 <code>cftransaction</code> so that they execute as a unit. Code Overview: <pre><code><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> </code></pre> I then return the selectQuery resultset to my app which uses it for outputting some data. How would I accomplish the same thing in a single SQL Server 2008 stored procedure that I could call using <code>cfstoredproc</code>? Again, I am thinking that the native CF way (with <code>cfquery</code>) is not as efficient as a stored procedure since I have to retrieve the resultset back to CF, then call another query back to the DB. A single stored procedure does everything in the DB and then returns the original query resultset for use. Any ideas?
Tags
<sql-server><tsql><stored-procedures><coldfusion>
Title
Updating table based on Select query in stored procedure / ColdFusion
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USmarc_s
UserOwnerUserId
1. USBryan Lewis
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.