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
below 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:09:51 GMT
 Please help to convert view on views to stored procedure

paul,

what u need are parametrized stored procs.
mssql calls them 'inline table valued functions',
you create them with something like:

create function dbo.View1 (


)
returns table as return
     select a,b,c,d
        from tMyTable1


greeings

selecting on them you need to prepone 'dbo.' allways:

select * from dbo.View1(yourValue1,yourValue2)

      robert



Fri, 08 Oct 2004 19:44:20 GMT
 Please help to convert view on views to stored procedure
paul,

what u need are parametrized stored procs.
mssql calls them 'inline table valued functions',
you create them with something like:

create function dbo.View1 (


)
returns table as return
     select a,b,c,d
        from tMyTable1


selecting on them you need to prepone 'dbo.' allways:

   select * from dbo.View1(yourValue1,yourValue2)

greeings

      robert



Fri, 08 Oct 2004 19:45:05 GMT
 Please help to convert view on views to stored procedure
Thanks Robert,

I eventually managed to get it to work.

Quote:

> paul,

> what u need are parametrized stored procs.
> mssql calls them 'inline table valued functions',
> you create them with something like:

> create function dbo.View1 (


> )
> returns table as return
>      select a,b,c,d
>         from tMyTable1


> greeings

> selecting on them you need to prepone 'dbo.' allways:

> select * from dbo.View1(yourValue1,yourValue2)

>       robert



Sat, 09 Oct 2004 17:01:56 GMT
 
 [ 4 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. views and stored procedures - A CRY FOR HELP!

9. Help !!Decrypting Stored Procedures and Views

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