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.MyProcedure1
(@newsletterid int)
AS
SET NOCOUNT ON
-- Return Subscribers
declare @howmany int
set @howmany=isnull((select count(subscriberid) from mySubscribers where
newsletterid=@newsletterid),0)
update Mynewsletters set status=3,howmany=@howmany where
newsletterid=@newsletterid
select @howmany

exec MyProcedure2

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

[rajithprabhakar]

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

[Erland Sommarskog]

Trevor Bishop (tr**********@viper.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Trevor Bishop]

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]

Trevor Bishop (tr**********@viper.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx