Calling an SP from another SP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Calling an SP from another SP?

Author

Topic

fizzer666
Starting Member

6 Posts

Posted - 2006-05-25 : 11:36:29

Hi,

This is my first post so please bare with me if I'm in the wrong forum.

I'm quite new to SQL Server and am trying to do the following:

I have three tables - Products, Transactions and Deliveries. I want to return all products regardless of whether thay have a transaction or not for a particular delivery. So the DeliveryID has to be the parameter. I have tested the query side of things by using two views as below (View 1 calling View 2 to get a result)

View1

SELECT     p.ProductRef, ISNULL(t.QtyDelivered, 0) AS Delivered, ISNULL(t.QtyReturned, 0) AS Returned, ISNULL(t.QtySampled, 0) AS Sampled
FROM         dbo.Products AS p LEFT OUTER JOIN
                      dbo.View2 AS t ON p.ProductID = t.ProductID

View2

SELECT     TransactionID, ProductID, QtyDelivered, QtyReturned, QtySampled, Amount, Date, DeliveryID, TransactionGroupID
FROM         dbo.Transactions
WHERE     (DeliveryID = 2)

The data returned is exactly what I want. However, I need to set DeliveryID as a paramater so I think I need to use two stored procedures. One calling the other. I'm not sure if this is even possible? I've created the two SP's and tried running them in some VB code but I get an error when the first SP calls the second SP.

I originally tried to get the data with two Outer joins so I could use a single SP but this got too complicated for me!

I guess my first question to get me to the next stage is to clarify whether it is possible to call an SP from within another SP?

Many thanks in advance.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-05-25 : 11:43:54

there are many ways this can be accomplished. One possibility is to use a UDF in place of the view. This would effectively give you a parameterized view, which could then be referenced in the FROM clause of other select statements.

check out this article http://www.sqlteam.com/item.asp?ItemID=1955 regarding UDFs. There are several different kinds of UDFs, the one I am referring to is usually called an inline table valued function.

-ec

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-25 : 14:26:56

>>I guess my first question to get me to the next stage is to clarify whether it is possible to call an SP from within another SP?

exec sp_name @Variables_Go_here

fizzer666
Starting Member

6 Posts

Posted - 2006-05-26 : 09:45:43

Thanks for the replies,

Went down the UDF road in the end. Works a treat.

Glad to see that my first post in this forum was answered so quickly. Definately use again.

Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources