How Create a store procedure from another store procedure
-
i can create a view from another view for example :
create view viewX
as
select o.Locationid,o.Itemcode,o.openingdate,o.openingQty
from view1 as o
………..
left join view2 as MR on o.Locationid=MR.Locationid
left join view3 as TR on o.Locationid=TR.Locationid
left join view4 as TI on o.Locationid=TI.Locationid …….
where .......
so if i use here store procedure, how can i do this as above?
..Better Than Before... -
DECLARE @stmt NCHAR(1000)
SET @stmt = 'CREATE VIEW....'
EXEC (@stmt)
If you want this on a regular basis, you might want to take a look at sp_executesql in BOL. It has some advantages over the simple exec.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You may consider using table valued function instead of a stored procedure.
If You need to join on the result returned from the sp.
/rockmoose
You must unlearn what You have learnt -
What abot the "table valued function" ?
can u clear it little bit?
..Better Than Before... -
What about the "table valued function" ?
can u clear it little bit?
..Better Than Before... -
I think rockmoose means a user-defined function that returns a table. From BOL
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes, that was what I meant.
/rockmoose
Franks example could be written ( more lazily, if you don't want to spec the table 
as:CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS TABLE
AS
RETURN
SELECT
S.ShipperID,
S.CompanyName,
O.OrderID,
O.ShippedDate,
O.Freight
FROM
Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE
O.Freight > @FreightParm
Observe: different query plans are made by SQL Server for the 2 examples, I am not sure if performance is an issue here.
/rockmoose
You must unlearn what You have learnt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply