sql server programming: Combining Stored Procedure and a View together -- sql server programming

Combining Stored Procedure and a View together

Rex
7/19/2007 11:31:39 PM

sql server programming:

Hi I have a View and a stored procedure as follows:

ALTER PROCEDURE dbo.LettersandLabelsinfo
AS
BEGIN

SET NOCOUNT ON;

SELECT Family.familyID
, MAX(CASE WHEN Member.memType = 0 then Member.memType end) as
'Mother1 IndiviualID'
, MAX(CASE WHEN Member.memType = 1 then Member.memType end) as
'Father1 IndiviualID'
FROM Family LEFT OUTER JOIN Member ON Family.familyID =
Member.familyID LEFT OUTER JOIN Address ON Member.memberID =
Address.memberID Group BY Family.familyID order BY Family.familyID

and

SELECT dbo.View1.familyID, dbo.Address.address1,
dbo.Address.address2
FROM dbo.View1 INNER JOIN
dbo.Address ON dbo.View1.memberID =
dbo.Address.memberID

Common fields: Family.familyID and View1.familyID

My Question

How do I combine this two with common fields

Cheers..

Re: Combining Stored Procedure and a View together

Pawel Potasinski
7/20/2007 12:00:00 AM

You can declare a variable of table data type (with the same set of columns
as a result of SELECT statement inside the stored procedure). Then you can
use INSERT INTO...EXEC statement to fill the variable and then you can
simply JOIN table variable and view.

DECLARE @table TABLE (familyID int, motherID int, fatherID int)

INSERT INTO @table (familyID int, motherID int, fatherID int)
EXEC dbo.LettersandLabelsinfo

SELECT *
FROM @table t
INNER JOIN dbo.YourView v
ON t.familyID = v.familyID

--
Regards
Pawel Potasinski

Uzytkownik "Rex" <rakeshv01@gmail.com> napisal w wiadomosci
news:1184913099.038673.49980@e16g2000pri.googlegroups.com...
[quoted text, click to view]