I want to insert data into a temp Table using an Insert Exec statement such
as
insert #Mytable(Col1,...)
exec mysstoreProcedure @.var1
And I have the following message :
Server: Msg 8164, Level 16, State 1, Procedure ps_hasThisSolution, Line 25
An INSERT EXEC statement cannot be nested.
What is the problem ?This is a multi-part message in MIME format.
--=_NextPart_000_0012_01C37912.1B9AAE00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi ! Following you can seee the 2 procedures :
CREATE Procedure ps_hasThisSolution(
@.p_Client varchar(32) =3D NULL,
@.p_Produit varchar(32) =3D NULL,
@.hasSol int output
)
AS
create table #temp_sw_2 (
CODCLI varchar(10),
CODLOG varchar(10),
VERLOG float,
TYPE varchar(1),
CREATION smalldatetime,
DTEACHAT smalldatetime,
DTEEXPIR smalldatetime,
TYPE_ORDINATEUR varchar(30)
)
SET NOCOUNT ON
declare @.tmp varchar(50)
insert #temp_sw_2(CODCLI, =CODLOG,VERLOG,TYPE,CREATION,DTEACHAT,DTEEXPIR,TYPE_ORDINATEUR)
exec ps_getProductBySolution @.p_Client
if exists (SELECT *
FROM produit_version INNER JOIN
#temp_sw_2 ON produit_version.code_version =3D #temp_sw_2.CODLOG
WHERE #temp_sw_2.CODLOG =3D @.p_Produit )
select @.hasSol =3D 1
else
select @.hasSol =3D 0
return
And the 2nd procedure :
CREATE Procedure ps_getProductBySolution(
@.p_Client varchar(10) =3D Null
)
AS
declare @.p_solution varchar(10), @.p_verlog float, @.p_creation =smalldatetime, @.p_dteachat smalldatetime, @.dteexpir =smalldatetime,@.dteexpir_min smalldatetime
declare @.type_ordinateur varchar(30),@.type_produit varchar(10)
exec ps_getSolution @.p_Client, @.p_solution output, @.p_verlog output, =@.p_creation output, @.p_dteachat output, @.dteexpir output, @.dteexpir_min =output,@.type_ordinateur output,@.type_produit output
if @.p_solution is NULL begin
SELECT WCM.CODCLI, produit_version.code_produit, WCM.VERLOG, WCM.TYPE, =WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur
FROM produit_version INNER JOIN
WCM ON produit_version.code_version =3D WCM.CODLOG INNER JOIN produit ON
produit_version.code_produit=3Dproduit.code_produit AND =produit.type_produit=3D'LG'
WHERE (WCM.CODCLI =3D @.p_Client) end
else
begin
SELECT WCM.CODCLI,produit_version.code_produit, WCM.VERLOG, WCM.TYPE, =WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur
FROM produit_version INNER JOIN
WCM ON produit_version.code_version =3D WCM.CODLOG INNER JOIN
produit ON produit.code_produit =3D WCM.CODLOG AND produit.type_produit ==3D'LG'
WHERE (WCM.CODCLI =3D @.p_Client) AND produit_version.code_produit NOT IN
(SELECT produit_plus.code_produit
FROM produit_plus
WHERE produit_version.code_produit =3D produit_plus.code_produit
AND (produit_plus.type =3D 'SL' OR
produit_plus.type =3D 'SP') AND produit_plus.produit_type =3D 'LG' AND produit_plus.Code_plus ==3D @.p_solution)
UNION
SELECT @.p_Client, @.p_solution, @.p_verlog, null ,@.p_creation, =@.p_dteachat, @.dteexpir,@.type_ordinateur
end
return
"jeff" <jeff@.hotmail.com> wrote in message =news:02ec01c37888$83c24c20$a101280a@.phx.gbl...
> Perheps the mysstoreProcedure procedure too uses Insert > into ... exec <proc> to insert data in some table, is it > true?
> > Can you post the entire procedure code?
> > >--Original Message--
> >Hello,
> >I want to insert data into a temp Table using an Insert > Exec statement such
> >as
> >insert #Mytable(Col1,...)
> >exec mysstoreProcedure @.var1
> >
> >And I have the following message :
> >Server: Msg 8164, Level 16, State 1, Procedure > ps_hasThisSolution, Line 25
> >An INSERT EXEC statement cannot be nested.
> >
> >What is the problem ?
> >
> >
> >.
> >
--=_NextPart_000_0012_01C37912.1B9AAE00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi !
Following you can seee the 2 procedures =:
CREATE Procedure ps_hasThisSolution(@.p_Client varchar(32) =3D NULL,@.p_Produit varchar(32) =3D =NULL,@.hasSol int output)AS
create table #temp_sw_2 (CODCLI varchar(10),CODLOG varc=har(10),VERLOG float,TYPE =;varchar(1),CREATION smalldatetime,DTEACHAT =;smalldatetime,DTEEXPIR smalldatetime,TYPE_ORDINATEUR&=nbsp;varchar(30))SET NOCOUNT ONdeclare @.tmp varchar(50)
insert #temp_sw_2(CODCLI, CODLOG,VERLOG,TYPE,CREATION,DTEACHAT,DTEEXPIR,TYPE_ORDINATEUR)exec ps_getProductBySolution @.p_Client
if exists (SELECT * FROM produit_version INNER JOIN #temp_sw_2 ON produit_version.code_versio=n =3D #temp_sw_2.CODLOG WHERE =#temp_sw_2.CODLOG =3D @.p_Produit ) select @.hasSol =3D 1 else select @.hasSol =3D 0return
And the 2nd procedure :
CREATE Procedure ps_getProductBySolution(@.p_Client varchar(10) =3D Null)AS declare @.p_solution varchar(10), @.p_verlog =float, @.p_creation smalldatetime, @.p_dteachat smalldatetime, @.dteexpir smalldatetime,@.dteexpir_min smalldatetime declare =@.type_ordinateur varchar(30),@.type_produit varchar(10)
exec ps_getSolution =@.p_Client, @.p_solution output, @.p_verlog output, @.p_creation output, @.p_dteachat =output, @.dteexpir output, @.dteexpir_min output,@.type_ordinateur =output,@.type_produit output
if @.p_solution is NULL begin SELECT WCM.CODCLI, =produit_version.code_produit, WCM.VERLOG, WCM.TYPE, WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur FROM produit_version INNER JOIN WCM ON = produit_version.code_version =3D WCM.CODLOG INNER JOIN produit =ON produit_version.code_produit=3Dproduit.code_produit AND produit.type_produit=3D'LG' WHERE (WCM.CODCLI =3D @.p_Client) end else begin
SELECT WCM.CODCLI,produit_version.code_produit, WCM.VERLOG, WCM.TYPE, =WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur FROM =produit_version INNER JOIN WCM ON produit_version.code_version =3D WCM.CODLOG =INNER JOIN produit ON = produit.code_produit =3D WCM.CODLOG AND =produit.type_produit =3D'LG' WHERE (WCM.CODCLI =3D @.p_Client) AND produit_version.code_produit NOT IN (SELECT produit_plus.code_produit FROM produit_plus WHERE produit_version.code_produit =3D produit_plus.code_produit &n=bsp; AND (produit_plus.type =3D 'SL' OR produit_plus.type =3D 'SP') AND produit_plus.produit_type =3D 'LG' AND produit_plus.Code_plus =3D @.p_solution) UNION SELECT @.p_Client, =@.p_solution, @.p_verlog, null ,@.p_creation, @.p_dteachat, @.dteexpir,@.type_ordinateur end return
"jeff"
--=_NextPart_000_0012_01C37912.1B9AAE00--|||<snip>
There is a third procedure involved - ps_getsolution. You must check the
entire execution chain.
No comments:
Post a Comment