Please help to convert view on views to stored procedure 
Author Message
 Please help to convert view on views to stored procedure

Hi,

I have a view (called FinalView) that is a combined view of two other
views (View2 and View3). A

fourth view (View1) is also used in creating View2 and View3. In
View1, View2 and View3 I have specified criteria for the WHERE clause.
I developed these views to see if I could get something working based
on a set of known criteria using the view builder in Enterprise
Manager. However, I need now to convert these views into a stored proc
where I can pass in parameters in place of the hardcoded criteria. I'm
pretty new to SQL server and whilst I can create pretty simple stored
procs that accept parameters I'm I'm stuck on how to do this (nested
selects?). I've posted the views below. View1 retrieves a set of IDs
essentially. View2 and View3 returns matches to those IDs based on
differing criteria for the same fields. I use FinalView to compare the
two results sets from View2 and View3. The criteria I need to replace
with parameters are indicated by 'x', 'y', 'something',
'somethingElse' and 'somethingElseAgain'

--View1
SELECT
   KWs.KID,
   KWTT.KT_L,
   KWTT.KT_T,
   KT.KWT_ID,
   KWs.PID
FROM KeyWordType INNER JOIN
   KWs ON KT.KWT_ID = KWs.KWT_ID INNER JOIN
   KWTT ON KT.KWT_ID = KWTT.KWT_ID
WHERE
   (KWs.PID = 0) AND
   (KWTT.KT_L = N'x') AND
   (KWTT.KT_T = N'something' OR
   KWTT.KT_T = N'somethingElse' OR
   KWTT.KT_T = N'somethingElseAgain')

--View2
SELECT
   View1.KID,
   KWTT.KT_L,
   KWTT.KT_T,
   KTT.KWT_L,
   KTT.KWT_T,
   View1.PID
FROM View1 LEFT OUTER JOIN
   KTT ON View1.KID = KTT.KID LEFT OUTER JOIN
   KWTT ON View1.KWT_ID = KWTT.KWT_ID
WHERE
   (KWTT.KT_L = N'x') AND
   (KTT.KWT_L = N'x')

--View3
SELECT
   View1.KID,
   KWTT.KT_L,
   KWTT.KT_T,
   KTT.KWT_L,
   KTT.KWT_T,
   View1.PID
FROM View1 LEFT OUTER JOIN
   KTT ON View1.KID = KTT.KID LEFT OUTER JOIN
   KWTT ON View1.KWT_ID = KWTT.KWT_ID
WHERE
  (KTT.KWT_L = N'y') AND
  (KWTT.KT_L = N'y')

--ViewFinal
SELECT
   View2.KID,
   View2.KT_L,
   View2.KT_T,
   View2.KWT_L,
   View2.KWT_T,
   View2.PID,
   View3.KT_L AS Expr1,
   View3.KT_T AS Expr2,
   View3.KWT_L AS Expr3,
   View3.KWT_T AS Expr4,
   View3.PID AS Expr5
FROM
   View2 LEFT OUTER JOIN View3 ON View2.KID = View3.KID

Thanks

Paul



Wed, 06 Oct 2004 00:11:02 GMT
 Please help to convert view on views to stored procedure

Paul,

Quote:
> I have a view (called FinalView) that is a combined view of two
other
> views (View2 and View3). A

> fourth view (View1) is also used in creating View2 and View3. In
> View1, View2 and View3 I have specified criteria for the WHERE
clause.
> I developed these views to see if I could get something working
based
> on a set of known criteria using the view builder in Enterprise
> Manager. However, I need now to convert these views into a stored
proc
> where I can pass in parameters in place of the hardcoded criteria.
I'm
> pretty new to SQL server and whilst I can create pretty simple
stored
> procs that accept parameters I'm I'm stuck on how to do this (nested
> selects?). I've posted the views below. View1 retrieves a set of IDs
> essentially. View2 and View3 returns matches to those IDs based on
> differing criteria for the same fields. I use FinalView to compare
the
> two results sets from View2 and View3. The criteria I need to
replace
> with parameters are indicated by 'x', 'y', 'something',
> 'somethingElse' and 'somethingElseAgain'

Views are just canned select statements. You can just plug in the body
of the views as derived tables. However, the resulting statement is
quite unwieldy and probably more complex than it needs to be. You
should considr revsiting the porblem to see how you can simplify it.

Linda

create procedure ListK (





) as

--ViewFinal
SELECT  View2.KID,
        View2.KT_L,
        View2.KT_T,
        View2.KWT_L,
        View2.KWT_T,
        View2.PID,
        View3.KT_L AS Expr1,
        View3.KT_T AS Expr2,
        View3.KWT_L AS Expr3,
        View3.KWT_T AS Expr4,
        View3.PID AS Expr5
FROM   (SELECT  View1.KID,
                KWTT.KT_L,
                KWTT.KT_T,
                KTT.KWT_L,
                KTT.KWT_T,
                View1.PID
        FROM   (SELECT  KWs.KID,
                        KWTT.KT_L,
                        KWTT.KT_T,
                        KT.KWT_ID,
                        KWs.PID
                FROM    KeyWordType
                JOIN    KWs
                ON      KT.KWT_ID = KWs.KWT_ID
                JOIN    KWTT
                ON      KT.KWT_ID = KWTT.KWT_ID
                WHERE   KWs.PID = 0




        ) View1
        LEFT OUTER JOIN KTT
        ON      View1.KID = KTT.KID
        LEFT OUTER JOIN KWTT
        ON      View1.KWT_ID = KWTT.KWT_ID


) View2
LEFT OUTER JOIN (
        SELECT  View1.KID,
                KWTT.KT_L,
                KWTT.KT_T,
                KTT.KWT_L,
                KTT.KWT_T,
                View1.PID
        FROM   (SELECT  KWs.KID,
                        KWTT.KT_L,
                        KWTT.KT_T,
                        KT.KWT_ID,
                        KWs.PID
                FROM    KeyWordType
                JOIN    KWs
                ON      KT.KWT_ID = KWs.KWT_ID
                JOIN    KWTT
                ON      KT.KWT_ID = KWTT.KWT_ID
                WHERE   KWs.PID = 0




        ) View1
        LEFT OUTER JOIN KTT
        ON      View1.KID = KTT.KID
        LEFT OUTER JOIN KWTT
        ON      View1.KWT_ID = KWTT.KWT_ID


) View3
ON      View2.KID = View3.KID
go



Wed, 06 Oct 2004 23:55:25 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Please help to convert view on views to stored procedure

2. Please Help w/ Checking out Stored Procedures from InterDev Data View

3. stored procedure for viewing views...

4. converting access query to ms-sql view or stored procedure

5. temporary tables in stored procedures/parameter views vs stored procedures

6. Stored procedure dependancy on tables, views and other stored procedures

7. Query/View/Stored Proc structure - please help!?

8. Help !!Decrypting Stored Procedures and Views

9. views and stored procedures - A CRY FOR HELP!

10. View what stored procedures are currently in the procedure cache

11. Views on Views - recommendations please

12. Stored Procedures & Views - where are they stored


 
Powered by phpBB® Forum Software