Sometimes I need to select from sql based on criteria from another query. In certain situations, you can use the server side scripting to manipulate and call queries, but other times for performance, the best way is to have SQL server handle the workload.
I can declare a variable and set it from one query, and use it in another. For example, I want to check if there are only one record for an RID code passed in as a parameter, and then update the record if so:
declare @RecordCount int set @RecordCount = (select count(*) FROM [My].[dbo].[Table] where RID= @RID)
IF @RecordCount == 1 UPDATE [My].[dbo].[Table] SET myBitField= 1 WHERE RecordId= @RecordId