Calling a Proc From a Proc

[Trevor Bishop]

I'm having problems calling my second proc (MyProcedure2) from within an
existing proc. MyProcedure2 does not seeem to fire this way. My code is
below. Help appreciated.

Thanks,
Trevor

ALTER procedure dbo.MyProcedure 1
(@newsletterid int)
AS
SET NOCOUNT ON
-- Return Subscribers
declare @howmany int
set @howmany=isnull ((select count(subscribe rid) from mySubscribers where
newsletterid=@n ewsletterid),0)
update Mynewsletters set status=3,howman y=@howmany where
newsletterid=@n ewsletterid
select @howmany

exec MyProcedure2



*** Sent via Developersdex http://www.developersdex.com ***

[rajithprabhakar@gmail.com]

Re: Calling a Proc From a Proc

hi
the details of proc2 u didnt given then how any one help u...

[Erland Sommarskog]

Re: Calling a Proc From a Proc

Trevor Bishop (trevorbishop@v iper.com) writes:

I'm having problems calling my second proc (MyProcedure2) from within an
existing proc. MyProcedure2 does not seeem to fire this way. My code is
below. Help appreciated.

Your code looks OK, that all I can say. But I have no idea of what you
are trying to achieve. Maybe you should be more specific.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Trevor Bishop]

Re: Calling a Proc From a Proc

Sorry, my second proc does not receive values (input or output) from the
first proc - it jut needs to fire after the first one completes. The
second proc is just an update statement that pulls its select list from
a couple views.

Is there a reason with this structure that the 2nd proc would not fire?

Help appreciated.
Trevor



*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Re: Calling a Proc From a Proc

Trevor Bishop (trevorbishop@v iper.com) writes:

Sorry, my second proc does not receive values (input or output) from the
first proc - it jut needs to fire after the first one completes. The
second proc is just an update statement that pulls its select list from
a couple views.
>
Is there a reason with this structure that the 2nd proc would not fire?

I can only think of two reasons why the second procedure would not be
called:

1) There is a batch-aborting error.
2) The table you update has a trigger which rollbacks or commits the
transaction. If a trigger terminates with a different trancount than
it started with, the batch is aborted.

How have you certified that the second stored procedure is never invoked?
Did you use Profiler to see what was actually executed? Or do you just
"feel" that it was never called?

Which version of SQL Server are you using?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx