Joining an SP & an table within an SP

[1DMF]

Hi,

I want to write an SP that has a join and that join needs to use another SP.

I understand that you cannot join directly to an SP, and need to create a table on the fly, but don't know where to start.

So your help is appreciated.

I have

SELECT [FirstName] + ' ' + [LastName] AS Adviser, Contacts.CompanyName, Compliance_Audit.[CDate Visited], spRAG_Ratings.RAG, Compliance_Audit.[Client Name], Compliance_Audit.Case_Check_Hist, Compliance_Audit.Case_Class, Compliance_Audit.Rating, Compliance_Audit.Final_Rating, Compliance_Audit.Case_ID,Compliance_Audit.[Compliance Notes],Compliance_Audit.Actions
FROM ((Compliance_Audit LEFT JOIN Contacts ON Compliance_Audit.ContactID = Contacts.ContactID) LEFT JOIN spRAG_Ratings ON Compliance_Audit.ContactID = spRAG_Ratings.ContactID)
WHERE Contacts.MembershipLevel = 'Adviser' Or Contacts.MembershipLevel='Trainee'
GROUP BY [FirstName] + ' ' + [LastName], Contacts.CompanyName, Compliance_Audit.[CDate Visited], spRAG_Ratings.RAG, Compliance_Audit.[Client Name], Compliance_Audit.Case_Check_Hist, Compliance_Audit.Case_Class, Compliance_Audit.Rating, Compliance_Audit.Final_Rating
HAVING Contacts.ContactID = @company AND Compliance_Audit.[CDate Visited] Between @startDate AND @endDate
ORDER BY [FirstName] + ' ' + [LastName];

I need to replace the 'spRAG_Ratings' part with the temporary table created from the results of the spRAG_Ratings stored procedure.

I guess the join also needs changing, so help in understanding how you do all this with T-SQL is appreciated.

Regards,
1DMF.


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

It's OK , worked it out thanks.

CREATE TABLE #RAG( 
    ContactID int,  
    RAG nvarchar(10),
    Val smallint
     
) 
 
INSERT INTO #RAG 
EXEC spRAG_Ratings
    
SELECT [FirstName] + ' ' + [LastName] AS Adviser, Contacts.CompanyName, Compliance_Audit.[CDate Visited], #RAG.RAG, Compliance_Audit.[Client Name], Compliance_Audit.Case_Check_Hist, Compliance_Audit.Case_Class, Compliance_Audit.Rating, Compliance_Audit.Final_Rating, Compliance_Audit.Case_ID,Compliance_Audit.[Compliance Notes],Compliance_Audit.Actions
FROM ((Compliance_Audit LEFT JOIN Contacts ON Compliance_Audit.ContactID = Contacts.ContactID) LEFT JOIN #RAG ON Compliance_Audit.ContactID = #RAG.ContactID)
WHERE Contacts.MembershipLevel = 'Adviser' Or Contacts.MembershipLevel='Trainee'
GROUP BY [FirstName] + ' ' + [LastName], Contacts.CompanyName,Contacts.ContactID, Compliance_Audit.[CDate Visited], #RAG.RAG, Compliance_Audit.[Client Name], Compliance_Audit.Case_Check_Hist, Compliance_Audit.Case_Class, Compliance_Audit.Rating, Compliance_Audit.Final_Rating,Compliance_Audit.Case_ID,Compliance_Audit.[Compliance Notes],Compliance_Audit.Actions
HAVING Contacts.ContactID = @company AND Compliance_Audit.[CDate Visited] Between @startDate And @endDate
ORDER BY [FirstName] + ' ' + [LastName];

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads