Join these 2 views and make one stored procedure

[drweb]

i have made 2 views and concted them and my query is working find, but i want to make a 1 stored procedure from these 2 views ...

View 1

SELECT     dbo.page_information.page_id
FROM         dbo.user_pages INNER JOIN
                      dbo.page_information ON dbo.user_pages.page_id = dbo.page_information.page_id
WHERE     (dbo.user_pages.e_id = 32)

View 2

SELECT     dbo.page_information.page_id AS Expr1
FROM         dbo.VIEW1 RIGHT OUTER JOIN
                      dbo.page_information ON dbo.VIEW1.page_id = dbo.page_information.page_id
WHERE     (dbo.VIEW1.page_id IS NULL)

Please can u hlp me out

[ck9663]

i have made 2 views and concted them and my query is working find, but i want to make a 1 stored procedure from these 2 views ...

View 1
SELECT dbo.page_information.page_id
FROM dbo.user_pages INNER JOIN
dbo.page_information ON dbo.user_pages.page_id = dbo.page_information.page_id
WHERE (dbo.user_pages.e_id = 32)


View 2
SELECT dbo.page_information.page_id AS Expr1
FROM dbo.VIEW1 RIGHT OUTER JOIN
dbo.page_information ON dbo.VIEW1.page_id = dbo.page_information.page_id
WHERE (dbo.VIEW1.page_id IS NULL)


Please can u hlp me out

may i know why you need to create a stored proc from this view? if your stored proc will just contain this view, stored proc might not be your solution...

-- C K

[drweb]

By combining these two views iam getting a result...

I am developing a complete webbased solution in which i am required to have all the sql quries in procedures not in views....

so i simple want to combine into one query and make a stored procedure.

[Delerna]

SELECT The,Fields,You,Want
FROM View1
TheJoinType View2 ON TheJoinCriteria
 

Where TheJoinType= LEFT JOIN or JOIN or whatever


Put that into a stored proc, or even another view
Use a stored proc if you want to parameterize the query
otherwise do it as another view


as another alternative you could do this

SELECT The,Fields,You,Want
FROM
(   SELECT dbo.page_information.page_id
    FROM dbo.user_pages 
    INNER JOIN dbo.page_information 
    ON dbo.user_pages.page_id =dbo.page_information.page_id
    WHERE (dbo.user_pages.e_id = 32)
) a
 
JOIN
 
(   SELECT dbo.page_information.page_id AS Expr1
    FROM dbo.VIEW1 
    RIGHT OUTER JOIN dbo.page_information 
    ON dbo.VIEW1.page_id = dbo.page_information.page_id
    WHERE (dbo.VIEW1.page_id IS NULL)
) b
ON a.JoinField=b.JoinField
 

That way there is only 1 view or 1 stored proc, whichever you decide to go with.
Of course if the other 2 views are likely to be used in other places then the way you have them now is the way to go

[ck9663]

if you're allowed to create function, try creating a function that returns a table...and call it as if it's a table/view

select * from mydb.dbo.myTableFunction() where....

-- ck

[drweb]

I dont want any view....

i only want is to make one query instead of these two's.

and after that i'll make it a stored procedure

[ck9663]

I dont want any view....

i only want is to make one query instead of these two's.

and after that i'll make it a stored procedure

created procedure syntax

-- CK

[drweb]

I know how to create a stored procedure but i have to make one query instead of two views...

[Delerna]

I know how to create a stored procedure but i have to make one query instead of two views...

Then I don't understand why this

 
SELECT The,FIELDS,You,Want
FROM View1
TheJoinType View2 ON TheJoinCriteria
 

didn't answer your question

[ck9663]

i have made 2 views and concted them and my query is working find, but i want to make a 1 stored procedure from these 2 views ...

View 1
SELECT dbo.page_information.page_id
FROM dbo.user_pages INNER JOIN
dbo.page_information ON dbo.user_pages.page_id = dbo.page_information.page_id
WHERE (dbo.user_pages.e_id = 32)


View 2
SELECT dbo.page_information.page_id AS Expr1
FROM dbo.VIEW1 RIGHT OUTER JOIN
dbo.page_information ON dbo.VIEW1.page_id = dbo.page_information.page_id
WHERE (dbo.VIEW1.page_id IS NULL)


Please can u hlp me out

Your View2 will always be empty. Why? Because you have a WHERE (dbo.user_pages.e_id = 32) condition on your View1.Since it's an INNER JOIN, your dbo.page_information.page_id in view1 will always be 32 which means this WHERE (dbo.VIEW1.page_id IS NULL) will always be FALSE

-- ck

[Delerna]

The query I gave wasn't meant to be used as is, I meant it as an example of how to join the two views together for use in a stored procedure.
Thats why I used generic terms instead of specific fields :-)

If the fields in the views aren't compatible for joining then you will need to provide fields in them that are. maybe you could do something such as

View 1 
SELECT 1 as J,dbo.page_information.page_id
FROM dbo.user_pages INNER JOIN
dbo.page_information ON dbo.user_pages.page_id = dbo.page_information.page_id
WHERE (dbo.user_pages.e_id = 32)
 
 
View 2 
SELECT 1 as J,dbo.page_information.page_id AS Expr1
FROM dbo.VIEW1 RIGHT OUTER JOIN
dbo.page_information ON dbo.VIEW1.page_id = dbo.page_information.page_id
WHERE (dbo.VIEW1.page_id IS NULL)
 

then you could join the two queries on field J.
I don't know, this is meant as a suggestion not a total solution to your specific problem. If the Idea helps then you will need to adjust it to suit your specific requirements. There is not enough info to provide any more that suggestions