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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-23 : 21:09:55
|
Scott Watermasysk writes "I have written two stored procedures. The first one "sp_get_parent_id" takes on two integer parameters @SectionID and @ConentID and returns a single integer record, parent_id. (I know it is only one record because I have create a unique index on the two fields sectionid and contentid call on.
What I am attempting to do is to use this procuded in another procedure. I am not sure if this is possible or if it is even the method I should be using. Can anyone fill me in? I am also not sure if the a stored procedure is capable of returning a "value". I would assume by default it would return a record set...Is this correct?
What the second procedure does (well...is supposed to do) is take the value of "parent_id" and return a recordset of all of the child_id's that are related to it.
This is for a site built on ASP. I know I could call the first stored procedure, get the value, and then call the second. Although it is a very simple procedure, I was hoping to have limit my number of database calls. Does this make sense?
Thanks in advance for any help or advice.
See below for my two procudures.
Regards,
Scott
-------------------------------------------------------------------
sp_get_parent_id
------------------------------------------------------------------
CREATE proc sp_get_parent_id
@SectionID int,
@ContentID int
AS
SELECT
parent_id
From
[dbo].[3A_Navigation] nav
Where
nav.sid_id = @SectionID
AND nav.cid_id = @ContentID
GO
----------------------------------------------------------------
sp_get_children ---DOES NOT WORK
As you will see, I attempted to set the value of the @Parent calling the
sp_get_parent_id stored procedure.
---------------------------------------------------------------
CREATE proc sp_get_Children
@SecID int,
@ConID int
AS
DECLARE @Parent int
Set @Parent = Exec sp_get_parent_id @SecID, @ConID
SELECT
nav.short_desc, nav.sid_id, nav.cid_id, page.page_name
From
[dbo].[3A_Navigation] nav with(nolock)
inner join [dbo].[3A_Pages] page with(nolock) on(nav.page_id
= page.page_id)
Where
nav.active_link = 1
AND nav.child_id = @Parent
GO" |
|
|
|
|
|
|
|
Strored Procedure with in a Stored Procedure