How to reuse a result in a stored procedure to feed into a second select in the same stored procedure

I'm writing a stored procedure that gets data for a whole screen. How can I re-use 开发者_JS百科the result of one select to feed into a second select? This for performance reasons

Simplified Example: I have 4 tables

1. Responsible
2. Action (has foreign key to Responsible)
3. Group (has foreign key to Responsible)
4. Case (has foreign key to group)

The stored procedure gets a caseid to retrieve the data

First select gets the details of the case, group and its related responsible record:

select 
   Case.Date,
   Case.Name,
   Group.Name,
   Responsible.Name
   Responsible.ResponsibleID
from Case
inner join Group on Group.GroupID=Case.GroupID
inner join Responsible on Responsible.ResponsibleID=Group.ResponsibleID
where CaseID=@CaseID

Second select must get all actions that the responsible is assigned to. We only have the caseID and therefor we must reconstruct the joins again:

select Action.*  
from Case
inner join Group on Group.GroupID=Case.GroupID
inner join Responsible on Responsible.ResponsibleID=Group.ResponsibleID
inner join Action  on Action.ResponsibleID=Responsible.ResponsibleID
where CaseID=@CaseID

If it would be possible to reuse the variable from the previous result it would be possible to create the following query which would probably be better for performance:

Select * from Action where ResponsibleID={ResultSet1}.ResponsibleID

Consider holding your results in a table variable. This will allow you to perform multiple SQL operations on that result set from your permanent tables.

DECLARE @Case TABLE
(
    [CaseDate] datetime,
    [CaseName]  varchar(100),
    [GroupName] varchar(100),
    [ResponsibleName] varchar(100),
    [ResponsibleID] int
)

--get all your case details
INSERT INTO @Case( [CaseDate],[CaseName],
                   [GroupName],[ResponsibleName],[ResponsibleID])
SELECT 
   Case.Date,
   Case.Name,
   Group.Name,
   Responsible.Name
   Responsible.ResponsibleID
FROM      Case
INNER JOIN Group ON Group.GroupID=Case.GroupID
INNER JOIN Responsible ON Responsible.ResponsibleID=Group.ResponsibleID
WHERE CaseID=@CaseID


--now get the Action details for the previous case; 
--are we absolutely sure there is only one row? then an INNER JOIN 
SELECT A.*  
FROM   Action  AS [A] 
INNER JOIN @Case AS C ON A.ResponsibleID=C.ResponsibleID

-- or guard against multiple results in @Case
SELECT A.* FROM Action AS [A] 
WHERE A.ResponsibleID = (SELECT TOP 1 ResponsibleID FROM @Case) 

If you are getting data for various parts of the screen at the same time, just run a single query. Yes - you will get the same data in the leading columns, but that is the tradeoff against going to the database server twice.

select
   [Case].Date,
   [Case].Name CaseName,
   [Group].Name GroupName,
   Responsible.Name ResponsibleName,
   Responsible.ResponsibleID,
   Action.*
from [Case]
inner join [Group] on [Group].GroupID=[Case].GroupID
inner join Responsible on Responsible.ResponsibleID=[Group].ResponsibleID
inner join Action  on Action.ResponsibleID=Responsible.ResponsibleID
where [Case].CaseID=@CaseID

Other things I noticed about your query

• Case is a reserved word. Use a different name, but I think that's not the real table name
• Group is also a reserved word.
• Name appears multiple times in the SELECT clause. Alias them so that the front end can access them predictably, instead of using the column position
• If we are merging the queries, make sure to list out (and alias if required) the columns from Action.* so as not to clash with the other columns