the world of database programming can be a dark and treacherous journey. when you first set out, your quest seems manageable. but as time progresses your start to realize you need more queries, more tables, etc, etc, to achieve your goal. well today i’m going teach you a little trick that might help you need one less stored procedure then you thought...

sometimes it’s true, you actually do need two queries to get the job done. but other times you can circumvent this by using something like a "join" or "inner join" query to merge two tables together and pull your results from both.

but the optimizer in you says that still isn’t enough...

so let’s say you need to first get an automatically generated ID number from a table and you need two parameters to get this data. then you need a subsequent query to update that record.

historically, i probably would have said that you need to run two separate SQL procedures to achieve this goal. but if i would say that now, i’d be a liar...

take a look at this:

CREATE PROCEDURE [dbo].[TESTING-UPDATE]
   (@who            [nvarchar](50),
    @which          [nvarchar](50)) 
 
AS UPDATE [dbo].[table-name]
SET someValue = 'new value'
FROM (
        SELECT TOP 1 (autoid) as nuID 
        FROM [dbo].[table-name]
        WHERE name = @user AND num = @which
        ORDER BY autoid DESC
      ) as tested
WHERE autoid = tested.nuID
GO

as you can see, i’m actually running two queries in one! the traditional FROM [table-name] statement has been replaced by an "inline SQL query". what happens is the database will see the FROM () parenthetical notation, and immediately execute the syntax between them. that statement then returns its result set into a named object (in this case its called "tested"). then the rest of the procedure will execute as normal, but with the additional data we got from our inline query.

this technique can be extremely powerful! i just learned about it this week and i have already removed 10 now unnecessary SQL queries from the database for a project at work. so keep that one in you SQL lexicon for later ;D