How to test an stored procedure that calls another stored procedure

May 5, 2011 at 1:43 PM

Let's say WeeklySales sproc (stored procedure) calls in its implementation DailySales sproc.

If I create a test sproc SQLTest_WeeklySales that calls WeeklySales sproc I got an error because you cannot invoke an sproc that already invokes another sproc.

How do I test WeeklySales sproc ?

May 5, 2011 at 1:50 PM

I have tested stored procedures that call other stored procedures and the only time I have hit a problem is if multiple stored procedures in the call stack are doing an INSERT INTO ... EXECUTE ... to put the results of a stored procedure into a table. That can't be nested. This is a limitation of SQL Server, not TST. In cases like that, I've had to rethink the problem and try to solve it in a different way.

 

Can you post the exact error message?

May 5, 2011 at 2:04 PM

 I got error "Error: 8164, An INSERT EXEC statement cannot be nested." when excuting following code :
 
 WHILE @StartDay <= @EndDay
 BEGIN
  INSERT INTO @WeeklySalesTable 
   EXEC [FCS].[dbo].[DailySales] @StartDay
  SET @StartDay = DATEADD(DAY,1,@StartDay)
 End 

May 5, 2011 at 2:37 PM

Does [FCS].[dbo].[DailySales] do an INSERT INTO ... EXECUTE ... inside of it.Or does the code calling the code you posted do that? If so, that is you problem.

 

Does the code work fine outside of the TST testing?

May 5, 2011 at 3:40 PM
Edited May 5, 2011 at 3:41 PM

 John thanks for your quick response and assistance.

[FCS].[dbo].[WeeklySales] does not do an INSERT INTO ... EXECUTE but the SQLTest_WeeklySales does as you can see below:

 -- This code is executed in SQLTest_WeeklySales stored procedure
 -- Store the actual data in #ExpectedResult
 INSERT INTO #ActualResult 
  EXEC [FCS].[dbo].[WeeklySales] @WeekEndingDate = '2011-05-03'

Do you know a workaround ?

May 5, 2011 at 3:57 PM

Do you need to have [FCS].[dbo].[DailySales] as a stored procedure or can you do it in a table value function? Or can [FCS].[dbo].[DailySales] pass back a table parameter?

May 5, 2011 at 4:00 PM

I found following workaround:

I replaced  

INSERT INTO #ActualResult
EXEC [FCS].[dbo].[WeeklySales] @WeekEndingDate = '2011-05-03'

To

 Insert Into #ActualResult
 SELECT a.*
 FROM OPENROWSET('SQLNCLI', 'Server=7JFMMN1\POSExpress;Trusted_Connection=yes;',
 'EXEC [bos].[Central].[WeeklySales] @WeekEndingDate = ''2011-05-03''') AS a;

And it works !!

May 5, 2011 at 4:01 PM

Thanks John for all your Help !

May 5, 2011 at 4:34 PM

I;m happy I was able to help out.