How to refer to previous result set in SP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to refer to previous result set in SP?

Author

Topic

MichaelG
Starting Member

11 Posts

Posted - 2008-09-19 : 08:21:35

I have a stored procedure returning two result sets. The second SELECT statement needs to refer to the first result set. Is there any way to refer to the first result set other than inserting it into a TABLE variable? What is the most efficient way to do this?

Thanks
Michael

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-19 : 08:25:28

read up on scope_identity() function

MichaelG
Starting Member

11 Posts

Posted - 2008-09-19 : 09:10:31

Not sure how that helps. SCOPE_IDENTITY() returns the last identity value inserted into a table. I'm not inserting anything. I merely need to refer to the result set from the previous SELECT statement in a JOIN of a second SELECT statement but also return both result sets from the stored procedure.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-19 : 09:14:30

Oh Paste some sample code. Here

If its what am thinking try the union all clause

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2008-09-19 : 09:15:24

If you only require one result set as the final output, you can use a CTE, derived table or nested query.

MichaelG
Starting Member

11 Posts

Posted - 2008-09-19 : 09:29:09

No, I need to return two result sets. Think master/detail except I need to start off by selecting the detail.

Here's what I have at the moment which works but I'd like to find a way to speed it up.

ALTER PROCEDURE [dbo].[TasksSelectByParentAndListAndCategory]
@ParentID int = 0,
@ListID int = 0,
@CategoryToMatch int = 0,
@FromID int = 0,
@ToID int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @tblStudentList TABLE (StudentID int)
DECLARE @tblTaskScores TABLE (InstanceID int, StudentID int, CriteriaID nvarchar(50), ScoreDate smalldatetime, Score nvarchar(150), Comment nvarchar(3800))

declare @TermStart smalldatetime
declare @TermEnd smalldatetime
SELECT @TermStart=TermStart, @TermEnd=TermEnd FROM Terms WHERE TermID = @ToID

WITH ContainedLists (ParentID, ListID, ListName, ValidFrom, ValidTo, ContextID, [Level])
AS
(
-- Anchor member definition
SELECT N.ParentID, N.ListID, N.ListName, N.ValidFrom, N.ValidTo, N.ContextID,
0 AS [Level]
FROM ListNames AS N
WHERE N.ParentID = @ParentID and N.ListID = @ListID
and N.ValidFrom <= @TermEnd and (N.ValidTo >= @TermStart or N.ValidTo is NULL)
UNION ALL
-- Recursive member definition
SELECT N.ParentID, N.ListID, N.ListName, N.ValidFrom, N.ValidTo, N.ContextID,
[Level] + 1
FROM ListNames AS N
INNER JOIN ContainedLists AS CL
ON N.ParentID = CL.ListID
WHERE N.ValidFrom <= @TermEnd and (N.ValidTo >= @TermStart or N.ValidTo is NULL)
)
-- Statement that executes the CTE
INSERT @tblStudentList

SELECT DISTINCT L.StudentID

FROM ContainedLists CL
INNER JOIN Lists L on L.ListID = CL.ListID

WHERE L.ValidFrom <= @TermEnd and (L.ValidTo >= @TermStart or L.ValidTo is NULL)
AND (CL.ContextID & 16) > 0

INSERT @tblTaskScores
SELECT TS.* FROM TaskScores TS
JOIN @tblStudentList S on S.StudentID = TS.StudentID
join TaskInstances TI on TI.InstanceID = TS.InstanceID
join AssessmentDefinitions AD on AD.DefinitionID = TI.DefinitionID

WHERE TI.TermID between @FromID and @ToID
AND (AD.CategoryID & @CategoryToMatch) > 0

Order by TS.StudentID, TI.TermID

SELECT TI.* FROM TaskInstances TI
right join (SELECT DISTINCT InstanceID FROM @tblTaskScores) TS on TS.InstanceID = TI.InstanceID

SELECT * FROM @tblTaskScores
END

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-19 : 09:31:42

Combine the 2 selects? turn them into views? Use the table variable you mentioned? Read the hint link in my sig?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-19 : 09:31:46

put the previous resultset into a temp table and select from there

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

MichaelG
Starting Member

11 Posts

Posted - 2008-09-19 : 10:28:09

Books Online says in most cases TABLE variable should be faster than a temporary table.

This is the problem I'm trying to solve:-

I have a table ListNames (ParentID int, ListID int, ListName, ValidFrom, ValidTo, ContextID) which stores lists of lists in a hierarchial manner.
e.g.
School
Subjects
English
Year 8
Class A // classes contain Students
Class B
Class C
Year 7
Class D
etc.

I have a table Lists (ListID, StudentID, ValidFrom, Valid To) which knows which class a student belongs to and when.

Students have assessments which are stored in a master table TaskInstances (InstanceID, DefinitionID, TermID, ParentID, ListID, TaskDate, TaskName, ...)
and a detail table TaskScores (InstanceID, StudentID, Score, Comment)

Finally, the table AssessmentDefinitions (CategoryID, DefinitionID, Description, ...) defines various types of assessment

I need to answer the question:- Find all assessments of type DefinitionID for Students in English/Year 8 for the last 4 years.

I start by finding the current students in English/Year 8. (the CTE)
I then find all TaskScores for those students over the last 4 years
I then find all TaskInstances for those scores

Remember students were not in the same class a year ago.

Step 2 can return a very large number of records

The results are transmitted to the client via WCF so I want to return TaskScores and TaskInstances separately to reduce size.

Any ideas how to do this efficiently?
Thanks
Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources