I am facing a weird problem when inserting data from an SQL Stored procedure into a table variable

Question

I have this stored procedure:

Lemma 1: exec GetData 1, '20220301', 1

I run it with 1 as one of the parameters, It does correctly return some data

Lemma 2: exec GetData 1, '20220301', 15

I run it with other parameter (15), it returns the same table and data as before, which is correct.

Now, for the VOODOO:

Lemma 3: we understand that the stored procedure is correctly returning the same dataset for the two different parameters, 1 and 15

So we go one step further, I want to insert the values into a table variable.

The problem is, with parameter 1, the table variable is being filled correctly. With parameter 15, although we have the same dataset as result, the table variable is not being filled.

example:

set nocount on
declare @tmp table (
         Id int primary key identity(1,1)
        ,ShoppingMallId int
        ,EntranceGateId int
        ,CameraId int
        ,[Period] bigint
        ,CountIn int
        ,CountOut int) 
insert into @tmp
exec GetData 1, '20220301', 1
select * from @tmp

In the above code, with 1 as parameter, the @tmp table is filled with the correct data.

In the below code, with parameter 15, no data is filled, although the exec is returning the data.

set nocount on
declare @tmp table (
         Id int primary key identity(1,1)
        ,ShoppingMallId int
        ,EntranceGateId int
        ,CameraId int
        ,[Period] bigint
        ,CountIn int
        ,CountOut int) 
insert into @tmp
exec GetData 1, '20220301', 15
select * from @tmp

Any ideas???

Answer 1

thank you all for your attempts to help me solve this problem, but the problem lies somewhere else. It was a misconception from my side.

From my point of view, my stored procedure "GetData" returns a dataset, and my misconception was that regardless of how that data was selected, once the data is available we should be able to insert it.

I was wrong!

The issue was this: my stored procedure gets the data from 2 different data sources. So for the value 1, I have data from source A, for the value 15, I have data source B. Until here, there is no problem, as both are retrieving same dataset, same parameters, same type.

The problem lies that in stored procedure B (the one used with parameter 15), there is also a temp table that I insert into

The problem: Nested Inserts.

Basic rule: 'We cannot insert into a temp table when we select from a stored procedure that also inserts into some temp table (nested inserts)'

It still is weird to me, because the dataset set is there.. But Bill knows better...

This was the error I found in my logs: Error number: 8164 Error message: An INSERT EXEC statement cannot be nested.