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
