[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Stored procedure is skipping a nested stored procedure

Hello,

When I call the attached stored procedure, EXEC sp_SupportTeamDataInsert is not being executed.  I have performed the following test with these results

In all cases EXEC sp_DistributionTableInsert executes.  However, it looks like EXEC sp_SupportTeamDataInsert is not executed.  When I execute just EXEC sp_SupportTeamDataInsert, that sproc works fine.  This is in a MS SQL Server 2005 environment
ALTER PROCEDURE [dbo].[sp_PopulateDistributionTable]
AS
TRUNCATE TABLE Distribution
BEGIN TRAN
	BEGIN TRY
		EXEC sp_DistributionTableInsert
		EXEC sp_SupportTeamDataInsert
		DELETE FROM Distribution Where LastPostedDate < POPStart 
		DELETE D FROM Distribution D
		INNER JOIN TaskOrderContributions TOC
		ON D.TaskOrder = TOC.TaskOrder
		WHERE TOC.Eligible = 0
		COMMIT TRAN
	END TRY
	
	BEGIN CATCH
		RAISERROR('Transaction Aborted', 16, 1)
		ROLLBACK TRANSACTION
	END CATCH



ALTER PROCEDURE [dbo].[sp_SupportTeamDataInsert]

AS

DECLARE curTaskOrder CURSOR FORWARD_ONLY
FOR SELECT DISTINCT TaskOrder from vwCSIB_ShareAllocationEligibleEmployees
DECLARE @TaskOrder NVARCHAR(50)
OPEN curTaskOrder
FETCH NEXT FROM curTaskOrder INTO @TaskOrder
WHILE @@Fetch_Status = 0
BEGIN
	EXEC spDistributionTableSupportTeamInsert @TaskOrder
	FETCH NEXT FROM curTaskOrder INTO @TaskOrder
END
CLOSE curTaskOrder
DEALLOCATE curTaskOrder 

GO

Open in new window

0
chtullu135
Asked:
chtullu135
  • 2
2 Solutions
 
radcaesarCommented:
what happens if you just execute sp_DistributionTableInsert?
0
 
roshnipatelCommented:
Try debugging it in pieces:

Run this first to see if it executes..
then add the DELETE STMTS,
then add your TRY CATCH.  

OR you can
execute the whole code block:

TRUNCATE TABLE Distribution
BEGIN TRAN
      BEGIN TRY
            EXEC sp_DistributionTableInsert
            EXEC sp_SupportTeamDataInsert
            DELETE FROM Distribution Where LastPostedDate < POPStart
            DELETE D FROM Distribution D
            INNER JOIN TaskOrderContributions TOC
            ON D.TaskOrder = TOC.TaskOrder
            WHERE TOC.Eligible = 0
            COMMIT TRAN
      END TRY
      
      BEGIN CATCH
            RAISERROR('Transaction Aborted', 16, 1)
            ROLLBACK TRANSACTION
      END CATCH

directly and set breakpoints to verify that it's being called.
ALTER PROCEDURE [dbo].[sp_PopulateDistributionTable]
AS
BEGIN
TRUNCATE TABLE Distribution

		EXEC sp_DistributionTableInsert
Print 'EXEC sp_DistributionTableInsert'
		EXEC sp_SupportTeamDataInsert
Print 'EXEC sp_SupportTeamDataInsert'

END

Open in new window

0
 
chtullu135Author Commented:
I found the problem - there was no problem save for staring at the code for two long.
0
 
radcaesarCommented:
Please explain the problem clearly.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now