Call a stored proc from another stored proc

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 2008 Forums

Transact-SQL (2008)

Call a stored proc from another stored proc

Author

Topic

kjk_kjp
Starting Member

18 Posts

Posted - 2010-08-04 : 16:00:51

What I need for my end result is: I need to return a row for each person who has applied since a certain date - info need for each record is: ID, Program, and a comma delimited list of Status History for this person - can not use a function because I'm running my stored procs against a remote database that I'm not allowed to create functions on....

I have the following stored proc (see **** within proc below for my question)

USE [live]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [dbo].[stp_Applicant-TEST]

AS
BEGIN
SET NOCOUNT ON;
EXEC('SELECT DISTINCT a.APPL_APPLICANT
,a.TERM
,a.PROGRAM
,****Question: How can I call my second stored proc to get my comma delimited list of Statuses for each row this returns or is there a way to get my comma delimited list of statuses another way? The status history that I am trying to get is in a different table on the same database?
FROM [ABClive].[dbo].[APPLICATIONS] a
WHERE a.APPL_DATE >= ''2009-05-01''
and a.APPL_CURRENT_STATUS in (''MS'', ''AD'', ''AC'')') AT remoteserver

END

I have the following stored proc (it returns one colum that is a comma delimited list of Statuses for a person)
USE [live]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [dbo].[stp_Applicant-Activity]
@ID varchar(10) = '',
@AppActivity varchar(2000) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
EXEC('
DECLARE @AppActivity varchar(2000)

SELECT @AppActivity = ISNULL(@AppActivity + '', '', '''') + CAST(APPL_STATUS as varchar(10)) + ''('' + CAST(APPL_STATUS_DATE as varchar(11)) + '')''
FROM [ABClive].[dbo].[STATUS_TABLE]
WHERE ID = ''' + @ID + '''
ORDER BY POS

SELECT @AppActivity') AT remoteserver

END

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-18 : 06:54:26

if the source table is small, use a view instead otherwise save the resultset from the 2nd procedure into a temporary table inside the calling procedure

--------------------
keeping it simple...

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 10:56:08

You would have to execute the second procedure first and place the results in a temp table. then you can join to it in the other query. (ie.

declare @t table ( field1 int, field 2 int, etc...)
        --then you can ...
        insert into @t
        exec [dbo].[stp_Applicant-Activity])

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources