Nested Stored Procedures - 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

General SQL Server Forums

New to SQL Server Programming

Nested Stored Procedures

Author

Topic

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-23 : 13:27:32

Ok, I've got another newbie question for SQl Server. I have created one stored procedure called Sp_SessionManager. In another stored procedure called Sp_SessionManager3 I need to be able to call this stored procedure. In MS Access you would do that like this [Sp_SessionManager].SessionMonth, to state that you are using SessionMonth from Sp_SessionManager. This apparently doesn't work in SQL Server 2005. I've looked every where and can't find the info I'm looking for. Anyone have any ideas. I'll paste my SQL code below.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Sp_SessionManager3]
@AircraftType nvarchar,
@SessionType nvarchar = "QLC",
@IssuedCA bit = "True",
@CrewPos1 nvarchar = "CA",
@IsVoid bit = "False",
@CrewPos2 nvarchar = "FO",
@IssuedFO bit = "True",
@CrewPos3 bit = "SO",
@IssuedSO bit = "True",
@CrewPos nvarchar
AS
SELECT
[Sp_SessionManager].tblSessionID.SessionID,
[Sp_SessionManager].SessionMonth,
[Sp_SessionManager].SessionType,
[Sp_SessionManager].Program,
[Sp_SessionManager].AircraftType,
[Sp_SessionManager].PilotEvaltrID,
[Sp_SessionManager].SOEvaltrID,
[Sp_SessionManager].IssuedCA,
@CrewPOs AS Expr1,
[Sp_SessionManager].IssuedFO,
[Sp_SessionManager].IssuedSO,
[Sp_SessionManager].CARandomID,
[Sp_SessionManager].FORandomID,
[Sp_SessionManager].SORandomID,
[Sp_SessionManager].IsVoid,
[Sp_SessionManager].MinLevel,
[Sp_SessionManager].FLTNbr,
[Sp_SessionManager].FltZDate
FROM [Sp_SessionManager]
WHERE [Sp_SessionManager].SessionType <> @SessionType
AND [Sp_SessionManager].AircraftType =@AircraftType
AND [Sp_SessionManager].IssuedCA=@IssuedCA
AND txtCrewPos = @CrewPos1
AND [Sp_SessionManager].CARandomID Is Null
AND [Sp_SessionManager].IsVoid=@IsVoid
OR [Sp_SessionManager].SessionType<>@SessionType
AND [Sp_SessionManager].AircraftType=@AircraftType
AND txtCrewPos=@CrewPos2
AND [Sp_SessionManager].IssuedFO=@IssuedFO
AND [Sp_SessionManager].FORandomID Is Null
AND [Sp_SessionManager].IsVoid=@IsVoid
OR [Sp_SessionManager].SessionType<>@SessionType
AND [Sp_SessionManager].AircraftType=@AircraftType
AND txtCrewPos=@CrewPos3
AND [Sp_SessionManager].IssuedSO=@IssuedSO
AND [Sp_SessionManager].SORandomID Is Null
AND [Sp_SessionManager].IsVoid=@IsVoid
ORDER BY [Sp_SessionManager].tblSessionID.SessionID DESC;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 13:34:45

to select fields from stored procedure resultset use the method with OPENROWSET in link below
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 13:47:03

I suggest Table Function. You can do something like this:

create function fn_sessionmanager (.....)
RETURNS TABLE
as
......
RETURN(SELECT .... FROM tab1 where ...)

-------FUNC 2
Create function fn_SessionManager3(....)
RETURNS TABLE
as
return(select .... from fn_sessionManager3(....) where .... order by .... )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources