Stored procedure in View? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procedure in View?

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-09 : 19:24:50

Is it possible to access a stored procedure within a view. I have a SP that provides me with a comma seperated list of data pertaining to a paticular item. I want to tie the info the SP returns to every row in my VIEW next to its item.

Thanks in Advance!
Lane

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-09 : 21:55:40

Not to my knowledge. However, since SPs can return recordsets....

What I would probably do in this case is create an SP which does the whole lot including...
another sp which returns the comma separated list, store in a temp table
do your join
then return then result

Wanna post some code?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-10 : 11:22:43

How would I incorporate this view into this SP? @LPP should be tied to lcampbel.LPP_ship_qty.partNo to

Here is my SP:

CREATE PROCEDURE dbo.get_LPP_comps
@lpp varchar(100)
AS
DECLARE @comp varchar(100)
SELECT @comp = COALESCE(@comp + ',', '') +
CAST(component AS varchar(20))
FROM mfgpro.dbo.psAna WHERE parent = + @lpp

SELECT @comp comp
GO

Here is my view:

CREATE VIEW dbo.LPP_ALL
AS
SELECT DISTINCT
TOP 100 PERCENT lcampbel.LPP_ship_qty.partNo AS item, MFGPRO.dbo.ANA.DESC1 AS Description, MFGPRO.dbo.ANA.PTDRAW AS Drawing,
MFGPRO.dbo.ANA.STATUS, lcampbel.LPP_ship_qty.[Qty Ship ], lcampbel.LPP_std_cost.[PO Cost]
FROM MFGPRO.dbo.ANA RIGHT OUTER JOIN
lcampbel.LPP_std_cost RIGHT OUTER JOIN
lcampbel.LPP_ship_qty ON lcampbel.LPP_std_cost.[Part #] = lcampbel.LPP_ship_qty.partNo ON
MFGPRO.dbo.ANA.ITEM = lcampbel.LPP_ship_qty.partNo
ORDER BY lcampbel.LPP_ship_qty.partNo

Thanks for your help!

Edited by - lane0618 on 05/10/2002 11:25:37

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-10 : 11:42:20

Take a look at these:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14095
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9978

AFAIK it can't be done in a single SELECT statement (so you cannot do it in a view) but it's very easy to put results in a temp table and SELECT from them in a stored procedure. There's a few different methods in those links, one or more of them will definitely work for you.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-10 : 11:51:08

How about using a derived table in the FROM clause.
You will have to check on the type of JOIN you need!

CREATE VIEW dbo.LPP_ALL
AS
SELECT DISTINCT
TOP 100 PERCENT lcampbel.LPP_ship_qty.partNo AS item, MFGPRO.dbo.ANA.DESC1 AS Description, MFGPRO.dbo.ANA.PTDRAW AS Drawing,
MFGPRO.dbo.ANA.STATUS, lcampbel.LPP_ship_qty.[Qty Ship ], lcampbel.LPP_std_cost.[PO Cost]
FROM MFGPRO.dbo.ANA RIGHT OUTER JOIN
lcampbel.LPP_std_cost RIGHT OUTER JOIN
lcampbel.LPP_ship_qty ON lcampbel.LPP_std_cost.[Part #] = lcampbel.LPP_ship_qty.partNo ON
MFGPRO.dbo.ANA.ITEM = lcampbel.LPP_ship_qty.partNo
JOIN (SELECT comp = COALESCE(@comp + ',', '') + CAST(component AS varchar(20))
FROM mfgpro.dbo.psAna) AS newtbl ON newtbl.comp = lcampbel.LPP_ship_qty.partNo
ORDER BY lcampbel.LPP_ship_qty.partNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources