Mulitple record sets problem

Mulitple record sets problem

Old forum URL: forums.lhotka.net/forums/t/3859.aspx

griff posted on Wednesday, November 07, 2007

Hi

I am having problems writing t-sql to return multiple records sets in a stored procedure so I can populate by BO with child objects. In the project tracker we have

ALTER PROCEDURE [dbo].[getProject]
    (
        @id uniqueidentifier
    )
AS
    SELECT Id,Name,Started,Ended,Description,LastChanged
    FROM Projects
    WHERE Id=@id

    SELECT ResourceId,LastName,FirstName,Assigned,Role,Assignments.LastChanged AS LastChanged
    FROM Resources,Assignments
    WHERE ProjectId=@id AND ResourceId=Id
    RETURN


I assume the last part <ResourceId=Id> the Id part is taken from the first SELECT statement.
I have tried to replicate this, different tables, but can't save the st. proc as it errors saying 'Invalid column name'  Here is my sql snippet:

select JobID,jobtypeid,.........,........    FROM [dbo].[tblJobs]
WHERE
    [JobID] = @ID

select jobid, jobdate,........... from tbljobdates where tbljobdates.jobid=jobtypeid

So I want the second record set to select records based on the value of the 'jobtypeid' returned form the first select statement.

Can anyone provide help directly on this and/or general t-sql regarding referencing fields/data from other areas in the t-sql (either direct help or internet site resource)

Many thanks
Richard








Marjon1 replied on Wednesday, November 07, 2007

Richard,

The second SELECT statement is completely idependent of the first one, you have to determine the link between the two records. The two queries in the project tracker are looking at completly different things (one is projects and the other is the reources & assignments; linked by the passed value of @ID).

To get what you want (i.e. second statement to be filtered by the value jobtypeid from your first query) you would need to something like this:

SELECT JobID, JobTypeId FROM tblJobs
WHERE JobId = @ID

SELECT JobId, JobDate FROM tblJobDates
JOIN tblJobs ON tblJobDates.JobTypeId = tblJobs.JobTypeId
AND tblJobs.JobId = @ID

Or you could put the value of JobTypeId into a variable in the stored proc and then use this, check out some SQL reference material on how to do that, looks something like (of the top of my head):

SELECT JobID, @JobTypeId = JobTypeId FROM tblJobs
WHERE JobId = @ID

SELECT JobId, JobTypeId, JobDate FROM tblJobDates
WHERE tblJobDates.JobTypeId = @JobTypeId

griff replied on Wednesday, November 07, 2007

Marjon
thanks for the reply, it's late now.......so I'll pick this up tomorrow and get back
Richard

Copyright (c) Marimer LLC