"TroyS" wrote:

> I have the following:
>
> CREATE proc_1
>    @fromdate datetime
>    @todate datetime
> AS
> .....
> .....
> .....
> INSERT #temp_table EXECUTE dbo.proc_2 (note, proc_2 has a 2 date parameters
> to supply as well; i'm trying to figure out how to pass the 2 date
> parameters; see  below)
> ......
> I'm getting syntax errors on the Insert into the temp table statement above
>
> ============================================================================
> stored procedure proc_2 also has a similar 2 parameters @fromdate and
> @todate to supply.
> I want to execute proc_2 using the same parameter values (@fromdate,
> @todate) supplied with executing proc_1
>
> I've tried using: INSERT #temp_resource_avail EXECUTE dbo.proc_2
> @fromdate=?, @todate=?
> as placeholders for the parameters, but i haven't been able to figure out
> the correct syntax.
> Any ideas?
>
>
>
>

"Ravi" <ravishank***@hotmail.com> wrote in message
news:D8F96038-92D6-4FEA-9A24-F128C0DED02C@microsoft.com...
> CREATE proc_1
>   @fromdate datetime
>   @todate datetime
> AS
>
> CREATE TABLE #temp_table
> (
> Define column to match the out put of proc_2
> )
> INSERT #temp_table EXECUTE dbo.proc_2 @fromdate,@todate
>
>
> Note : Only one INSERT INTo EXEC.. is allowed when you use stored
> procecure
> nested calls.
>
>
>
> --
> Thanks & Rate the Postings.
> -Ravi-
>
>
> "TroyS" wrote:
>
>> I have the following:
>>
>> CREATE proc_1
>>    @fromdate datetime
>>    @todate datetime
>> AS
>> .....
>> .....
>> .....
>> INSERT #temp_table EXECUTE dbo.proc_2 (note, proc_2 has a 2 date
>> parameters
>> to supply as well; i'm trying to figure out how to pass the 2 date
>> parameters; see  below)
>> ......
>> I'm getting syntax errors on the Insert into the temp table statement
>> above
>>
>> ============================================================================
>> stored procedure proc_2 also has a similar 2 parameters @fromdate and
>> @todate to supply.
>> I want to execute proc_2 using the same parameter values (@fromdate,
>> @todate) supplied with executing proc_1
>>
>> I've tried using: INSERT #temp_resource_avail EXECUTE dbo.proc_2
>> @fromdate=?, @todate=?
>> as placeholders for the parameters, but i haven't been able to figure out
>> the correct syntax.
>> Any ideas?
>>
>>
>>
>>

"TroyS" wrote:

> Ravi,
> thanks for the help, but i'm still getting this error:
>
> Server: Msg 8164, Level 16, State 1, Procedure ILG_Availability_ByResource,
> Line 28
> An INSERT EXEC statement cannot be nested.
> (0 row(s) affected)
> Stored Procedure: ILG_Reporting.dbo.MyProcTest
>  Return Code = 0
>
> Here is my stored proc:
>
> CREATE PROCEDURE MyProcTest
>  @fromdate datetime,
>  @todate datetime
> AS
>
> -- create Resource Info temp table
> CREATE TABLE #temp_ilg_resource_avail (
>  resname varchar(80),
>  reseuid int,
>  resavailability decimal(9,5),
>  restype varchar(100),
>  reslob varchar(100),
>  resregion varchar(100),
>  resbranch varchar(100),
>  wprd_id int,
>  msp_web_time_period_finish datetime)
>
> -- Get Resource Availability Info from sp
> INSERT #temp_ilg_resource_avail EXECUTE dbo.ILG_Availability_ByResource
> @fromdate,@todate
>
> I know you said only 1 Insert Into Exec.....but why am i violating that
> limitation? I'm not seeing it.....
> I've verified that the columns in the create temp table match the ones
> returned by EXECUTE dbo.ILG_Availability_ByResource if i run it separately
> .....
>
> In 1 other stored procedures i have, i have multiple of these: INSERT
> #temp_table EXECUTE dbo.stored_procedure
> into several different temp tables. The overall stored procedure executes
> the several INSERT INTO....EXECUTES while all of individual
> stored_procedures don't have parameters to supply....is the limitation due
> to the supplying of parameters....?
>
> thx for the assistance
>
> "Ravi" <ravishank***@hotmail.com> wrote in message
> news:D8F96038-92D6-4FEA-9A24-F128C0DED02C@microsoft.com...
> > CREATE proc_1
> >   @fromdate datetime
> >   @todate datetime
> > AS
> >
> > CREATE TABLE #temp_table
> > (
> > Define column to match the out put of proc_2
> > )
> > INSERT #temp_table EXECUTE dbo.proc_2 @fromdate,@todate
> >
> >
> > Note : Only one INSERT INTo EXEC.. is allowed when you use stored
> > procecure
> > nested calls.
> >
> >
> >
> > --
> > Thanks & Rate the Postings.
> > -Ravi-
> >
> >
> > "TroyS" wrote:
> >
> >> I have the following:
> >>
> >> CREATE proc_1
> >>    @fromdate datetime
> >>    @todate datetime
> >> AS
> >> .....
> >> .....
> >> .....
> >> INSERT #temp_table EXECUTE dbo.proc_2 (note, proc_2 has a 2 date
> >> parameters
> >> to supply as well; i'm trying to figure out how to pass the 2 date
> >> parameters; see  below)
> >> ......
> >> I'm getting syntax errors on the Insert into the temp table statement
> >> above
> >>
> >> ============================================================================
> >> stored procedure proc_2 also has a similar 2 parameters @fromdate and
> >> @todate to supply.
> >> I want to execute proc_2 using the same parameter values (@fromdate,
> >> @todate) supplied with executing proc_1
> >>
> >> I've tried using: INSERT #temp_resource_avail EXECUTE dbo.proc_2
> >> @fromdate=?, @todate=?
> >> as placeholders for the parameters, but i haven't been able to figure out
> >> the correct syntax.
> >> Any ideas?
> >>
> >>
> >>
> >>
>
>
>

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:613C8100-F5E6-40D9-B4FC-4A7759100F5E@microsoft.com...
> Hi
>
> This may show you what Ravi was explaining:
>
> CREATE PROCEDURE MyProcTest3  @fromdate datetime, @todate datetime
> AS
> SELECT  @fromdate, @todate
>
> CREATE PROCEDURE MyProcTest2
> @fromdate datetime,
> @todate datetime
> AS
> BEGIN
> -- create Resource Info temp table
> CREATE TABLE #temp_example2 (
> fromdate datetime,
> todate datetime
> )
>
> -- Get Resource Availability Info from sp
> INSERT #temp_example2 EXECUTE dbo.MyProcTest3 @fromdate,@todate
>
> SELECT @fromdate,@todate #temp_example2
> END
>
> CREATE PROCEDURE MyProcTest1
> @fromdate datetime,
> @todate datetime
> AS
> BEGIN
> -- create Resource Info temp table
> CREATE TABLE #temp_example1 (
> fromdate datetime,
> todate datetime
> )
>
> -- Get Resource Availability Info from sp
> INSERT #temp_example1 EXECUTE dbo.MyProcTest2 @fromdate,@todate
>
> SELECT @fromdate,@todate FROM #temp_example1
> END
>
> -- Works as no called procedure uses INSERT..EXEC
> EXEC MyProcTest2 '20050701', '20050702'
>
> -- Fails as MyProcTest2 uses INSERT..EXEC
> EXEC MyProcTest1 '20050701', '20050702'
>
>
> Therefore it looks like ILG_Availability_ByResource  or a procedure called
> by it, is
> using INSERT...EXEC
>
> John
>
>
> "TroyS" wrote:
>
>> Ravi,
>> thanks for the help, but i'm still getting this error:
>>
>> Server: Msg 8164, Level 16, State 1, Procedure
>> ILG_Availability_ByResource,
>> Line 28
>> An INSERT EXEC statement cannot be nested.
>> (0 row(s) affected)
>> Stored Procedure: ILG_Reporting.dbo.MyProcTest
>>  Return Code = 0
>>
>> Here is my stored proc:
>>
>> CREATE PROCEDURE MyProcTest
>>  @fromdate datetime,
>>  @todate datetime
>> AS
>>
>> -- create Resource Info temp table
>> CREATE TABLE #temp_ilg_resource_avail (
>>  resname varchar(80),
>>  reseuid int,
>>  resavailability decimal(9,5),
>>  restype varchar(100),
>>  reslob varchar(100),
>>  resregion varchar(100),
>>  resbranch varchar(100),
>>  wprd_id int,
>>  msp_web_time_period_finish datetime)
>>
>> -- Get Resource Availability Info from sp
>> INSERT #temp_ilg_resource_avail EXECUTE dbo.ILG_Availability_ByResource
>> @fromdate,@todate
>>
>> I know you said only 1 Insert Into Exec.....but why am i violating that
>> limitation? I'm not seeing it.....
>> I've verified that the columns in the create temp table match the ones
>> returned by EXECUTE dbo.ILG_Availability_ByResource if i run it
>> separately
>> .....
>>
>> In 1 other stored procedures i have, i have multiple of these: INSERT
>> #temp_table EXECUTE dbo.stored_procedure
>> into several different temp tables. The overall stored procedure executes
>> the several INSERT INTO....EXECUTES while all of individual
>> stored_procedures don't have parameters to supply....is the limitation
>> due
>> to the supplying of parameters....?
>>
>> thx for the assistance
>>
>> "Ravi" <ravishank***@hotmail.com> wrote in message
>> news:D8F96038-92D6-4FEA-9A24-F128C0DED02C@microsoft.com...
>> > CREATE proc_1
>> >   @fromdate datetime
>> >   @todate datetime
>> > AS
>> >
>> > CREATE TABLE #temp_table
>> > (
>> > Define column to match the out put of proc_2
>> > )
>> > INSERT #temp_table EXECUTE dbo.proc_2 @fromdate,@todate
>> >
>> >
>> > Note : Only one INSERT INTo EXEC.. is allowed when you use stored
>> > procecure
>> > nested calls.
>> >
>> >
>> >
>> > --
>> > Thanks & Rate the Postings.
>> > -Ravi-
>> >
>> >
>> > "TroyS" wrote:
>> >
>> >> I have the following:
>> >>
>> >> CREATE proc_1
>> >>    @fromdate datetime
>> >>    @todate datetime
>> >> AS
>> >> .....
>> >> .....
>> >> .....
>> >> INSERT #temp_table EXECUTE dbo.proc_2 (note, proc_2 has a 2 date
>> >> parameters
>> >> to supply as well; i'm trying to figure out how to pass the 2 date
>> >> parameters; see  below)
>> >> ......
>> >> I'm getting syntax errors on the Insert into the temp table statement
>> >> above
>> >>
>> >> ============================================================================
>> >> stored procedure proc_2 also has a similar 2 parameters @fromdate and
>> >> @todate to supply.
>> >> I want to execute proc_2 using the same parameter values (@fromdate,
>> >> @todate) supplied with executing proc_1
>> >>
>> >> I've tried using: INSERT #temp_resource_avail EXECUTE dbo.proc_2
>> >> @fromdate=?, @todate=?
>> >> as placeholders for the parameters, but i haven't been able to figure
>> >> out
>> >> the correct syntax.
>> >> Any ideas?
>> >>
>> >>
>> >>
>> >>
>>
>>
>>

"TroyS" <troy.stau***@ilg.com> wrote in message
news:uFEFP7kfFHA.2424@TK2MSFTNGP09.phx.gbl...
>I have the following:
>
> CREATE proc_1
>   @fromdate datetime
>   @todate datetime
> AS
> ....
> ....
> ....
> INSERT #temp_table EXECUTE dbo.proc_2 (note, proc_2 has a 2 date
> parameters to supply as well; i'm trying to figure out how to pass the 2
> date parameters; see  below)
> .....
> I'm getting syntax errors on the Insert into the temp table statement
> above
>
> ============================================================================
> stored procedure proc_2 also has a similar 2 parameters @fromdate and
> @todate to supply.
> I want to execute proc_2 using the same parameter values (@fromdate,
> @todate) supplied with executing proc_1
>
> I've tried using: INSERT #temp_resource_avail EXECUTE dbo.proc_2
> @fromdate=?, @todate=?
> as placeholders for the parameters, but i haven't been able to figure out
> the correct syntax.
> Any ideas?
>
>
>