An INSERT EXEC statement cannot be nested

¿Alguna vez te ha pasado que dentro de un Proceso Almacenado (SP desde ahora) te da un error cuando llamas a otro SP? A mi me ha pasado varias veces, y siempre he encontrado como "esquivar" ese problema, pero en estea ocasión ya no me valía con "esquivar" el problema, ya que necesitaba SI o SI que me ejecutara el anidamiento de SP.

Hoy te propongo la solución que he encotrado al problema, no se si será la mejor, pero a mí me esta funcionando y el rendimiento del SP ha mejorado muhco, ha pasado de casi 17 segundos de ejecución a sólo 3.

Planteamiento del problema

Primero quiero explicar que es lo que yo necesitaba hacer, para que así te sea un poco más fácil comprender la lógica del código que te voy a proponer para solucionarlo.

Se necesita calcular el costo de una mensajeria, dependiendo del codigo postal, tipo de caja y día de reparto. Cada mensajero puede tener varias propiedades, por lo que encarece el costo, como puede ser la urgencia del reparto (mi caso, 4 horas o inmediato).

Para ello se han definido varias tablas, todo esto si que ya no es necesario explicar ni mostrar. Pero en el SP principal, se calcula primero el costo para la ruta por defecto, sin las propiedades. Para después saber cuántas propiedades tiene. Luego en un segundo SP, se calcula el costo de cada una de las propiedades, y aqui es donde comenzó el problema con el anidamiento de intrucciones INSERT. Ya que se pretendía algo como esto

insert @costos exec calculaPropiedad

Es en ésta líinea donde MS-SQL nos devolvía el error, ya que previamente hay otros INSERT en otras tablas dinámicas.

Reproduciendo el error

Para que entendais un poco mejor lo expuesto hasta ahora, voy a mostraros un ejemplo de cómo y cúando se obtiene el error

CREATE PROCEDURE CalculaCostoPropiedad @storeid varchar(30) AS
   declare @mytemp table (costo decimal(10,4), qty int)
   INSERT INTO @mytemp (costo, qty) VALUES (2,2)
   select * from @mytemp
go
CREATE PROCEDURE CalculaCosto @storeid varchar(30) AS
   declare @mytemp table (costo decimal(10,4), qty int)
   INSERT INTO @mytemp (costo, qty) VALUES (1,1)
   INSERT INTO @mytemp EXEC CalculaCostoPropiedad @storeid 
   SELECT * FROM @mytemp 
go
EXEC CalculaCosto '7131'
go
DROP PROCEDURE CalculaCosto, CalculaCostoPropiedad

Evidentemente esto es un ejemplo muy simple, pero ilustra lo que estamos explicando. Si no te da error, es precisamente por lo simple que es, pero si estas leyendo estas lineas seguro que has recibido el error que estoy tratando de explicar.

El porqué del error

Este error ocurre cuiando un SP tiene que insertar datos desde otro SP en una tabla, ya sea temporal, dinamica o de la base de datos. Esto no está permidido al intentar aumentar los datos de una cadena de procedimientos almacenados. Solo se permite hacer una inserción al mismo tiempo. Esta sería la explicación que encontrartemos en la documentación.

La solucion propuesta

Después de leer documentación, buscar en internet, y sobre todo, ir viendo posibilidades, me he quedado con esta. La mejor forma de solucionar este problema es tener una tabla en la base de datos en la cual se van a ir almacenando los costos. 

   CREATE TABLE mytemp (col1 int     NOT NULL,
                         col2 char(5) NULL,
                        ...)

Luego en los respectivos SP se hace el INSERT dentro de esta tabla temporal para luego realizar las consultas necesarias para sacar los costes. Un ejemplo de código podría ser algo como esto

CREATE PROCEDURE CalculaCostoPropiedad @storeid varchar(30) AS
   INSERT INTO mytemp (costo, qty)
      SELECT t.costo, s.qty
      FROM   mensajeros s
      JOIN   costos t ON t.mensajero_id = s.mensajero_id
      WHERE  s.stor_id = @storeid
go
CREATE PROCEDURE CalculaCosto @storeid varchar(30) AS
   INSERT INTO mytemp (costo, qty)
      SELECT t.costo, s.qty
      FROM   mensajeros s
      JOIN   costos t ON t.mensajero_id = s.mensajero_id
      WHERE  s.stor_id = @storeid
   EXEC SalesByStore_core @storeid
   SELECT * FROM mytemp 
   delete from mytemp where clienteid = @storeid 
go
EXEC CalculaCosto '7131'
go
DROP PROCEDURE CalculaCosto, CalculaCostoPropiedad

Evidentemente esto es un ejemplo muy simple, mis SP eran mucho mas grandes y complejos, Pero se ve claramente el pequeño cambio en los SP para solucionar el problema. Esper que estas líneas te hayan servido de ayuda, se que no soy un buen escritor, asi que si no entiendes algo, comenta para que te pueda resolver la duda. Como siempre os dejo algunos enlaces de interés, de donde mezclando de aqui y de allí más algo de mi cosecha es la solución que os propongo.

Happy codding

#SQL #errores

Actualizacion 23 de Enero, por aporte de Thirone Bermudez

No es necesario crear la tabla en la base de datos, ya que se puede hacer perfectametne con una tabla temporal de sesion. De hecho, en mi codigo, mejoro un poco ya que no necesito estar borrando las lineas de la tabla. Aqui os pongo el codigo con los cambios propuestos.

CREATE PROCEDURE CalculaCostoPropiedad @storeid varchar(30) AS
   INSERT INTO #mytemp (costo, qty)
      SELECT t.costo, s.qty
      FROM   mensajeros s
      JOIN   costos t ON t.mensajero_id = s.mensajero_id
      WHERE  s.stor_id = @storeid
go
CREATE PROCEDURE CalculaCosto @storeid varchar(30) AS
   CREATE TABLE #mytemp (costo int, qty int)
   INSERT INTO #mytemp (costo, qty)
      SELECT t.costo, s.qty
      FROM   mensajeros s
      JOIN   costos t ON t.mensajero_id = s.mensajero_id
      WHERE  s.stor_id = @storeid
   EXEC SalesByStore_core @storeid
   SELECT * FROM #mytemp 
   DROP TABLE #mytemp 
go
EXEC CalculaCosto '7131'
go
DROP PROCEDURE CalculaCosto, CalculaCostoPropiedad

De esta manera si se ejecuta el procedimiento varias veces la tabla es diferente en cada sesion, y aunque al finalizar la sesion la tabla se destruye, es mejor hacer el CROP TABLE #tabla para asi liberar el objeto correctamente despues de usarlo, me paso en mis pruebas, que en la misma venta de codigo si no hacia el DROP TABLE me reconocia como si fuera la misma sesion de trabajo y la tabla no me la dejaba crear de nuevo ya que ya estaba creada.