Invalid Object name '##Temporary_table'

Question

I have stored procedure, which works fine if executed by SQL Server management studio. When Calling it from ASP.net MVC app it throws temporary table exception like Invalid object name '##temp_demographic_data_set'.

Any Clue, Why this error occurs when execute from C#.

Below is the stored procedure Code:

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_get_demographic_history_csv]  
    @_startdate datetime,
    @_enddate datetime,
    @_nccs varchar(56),
    @_megacity varchar(56),
    @_10lac varchar(56),
    @_below10 varchar(56),
    @_rural varchar(56),
    @_state_megacity varchar(56) =NULL,
    @_state_10lac varchar(56) =NULL,
    @_state_below10 varchar(56) =NULL,
    @_state_rural varchar(56) =NULL,
    @_gender varchar(56),
    @_agegroup varchar(56),
    @limt int =NULL,
    @off_set int =NULL,
    @_Subscription_start datetime,
    @_Subscription_end datetime,
    @_demographic_field_id varchar(max),
    @templateid int
AS 
   BEGIN
      DECLARE @demographic_columns varchar(max);
      DECLARE @temp_query nvarchar(max);
      DECLARE @parameter_defination_list nvarchar(max);
      DECLARE @parameter_list nvarchar(max);

      -- Variables to check whether user is allowed to query on columns or not
      DECLARE @is_nccs_exist varchar(20);
      DECLARE @is_agegroup_exist varchar(20);
      DECLARE @is_stategroup_exist int;
      DECLARE @is_townclass_exist int;
      DECLARE @is_gender_exist varchar(20);

      DECLARE @demographic_view nvarchar(max);
      DECLARE @demographic_report varchar(max);
      DECLARE @record_count int;
      -- DECLARE @tableid varchar(56);

      SET  XACT_ABORT  ON
      SET  NOCOUNT  ON



    -- Set Defaults 
    SET @is_nccs_exist ='false';
    SET @is_agegroup_exist ='false';
    SET @is_stategroup_exist =0;
    SET @is_townclass_exist =0;
    SET @is_gender_exist ='false';


    -- Get values from the template
    SELECT  
            @is_gender_exist=max(CASE WHEN demofield.name='master_gender' THEN 'true' ELSE 'false' END),
            @is_townclass_exist=max(CASE WHEN demofield.name='master_town_class'  THEN 1 ELSE 0 END) ,
            @is_stategroup_exist=max(CASE WHEN demofield.name='master_state_group' THEN 1 ELSE 0 END),
            @is_agegroup_exist=max(CASE WHEN demofield.name='master_age_group' THEN 'true' ELSE 'false' END),
            @is_nccs_exist=max(CASE WHEN demofield.name='master_nccs' THEN 'true' ELSE 'false' END)         

    FROM dbo.template_demographic_fields template
    INNER JOIN demgraphic_fields demofield on template.field_id=demofield.id
    WHERE demofield.is_inventory_field=1 and (@templateid=0 OR template_id=@templateid);

    -- Update Variables
    SET @is_nccs_exist =COALESCE(@is_nccs_exist,'false');
    SET @is_agegroup_exist =COALESCE(@is_agegroup_exist,'false');
    SET @is_stategroup_exist=COALESCE(@is_stategroup_exist,0);
    SET @is_townclass_exist =COALESCE(@is_townclass_exist,0);
    SET @is_gender_exist =COALESCE(@is_gender_exist,'false');

    exec dbo.sp_split_string_into_rows @_demographic_field_id;

    -- Get Demographic columns from table
    select @demographic_columns= 
            (
                COALESCE(@demographic_columns+ ',', '') + 
                CAST( (case when return_as is not null then name+' as '+ '['+return_as+']'  else '['+name+']' end) AS VARCHAR(max))
            )        
    from dbo.demgraphic_fields
    where is_inventory_field=0 and ( @templateid=0 OR id in (select cast(split_data as int) from  ##temp_convert)); 


    set @demographic_columns=@demographic_columns+','+'state_group as state_group_code,town_class as town_class_code,
        nccs_group as nccs_group_code,age_group as age_group_code, sex as gender,file_date,member_id';

    select @demographic_report= 
            (
                COALESCE(@demographic_report+ ',', '') + 
                CAST( (case when return_as is not null then '['+return_as+']' else '['+name+']' end) AS VARCHAR(max))

            )        
    from dbo.demgraphic_fields
    where is_inventory_field=0 and ( @templateid=0 OR id in (select cast(split_data as int) from  ##temp_convert));


    select  @demographic_view=
        (
            COALESCE(@demographic_view+ ',', '') + 
            CAST( (case when return_as is not null then '['+return_as+'] VARCHAR(56),'  else '['+name+'] VARCHAR(56),' end) AS VARCHAR(max))
        )        
    from dbo.demgraphic_fields
    where is_inventory_field=0 and ( @templateid=0 OR id in (select cast(split_data as int) from  ##temp_convert)); 

    SET @demographic_view='CREATE TABLE ##temp_demographic_data_set ('+(@demographic_view+'state_group_code  VARCHAR(56), town_class_code  VARCHAR(56),
        nccs_group_code  VARCHAR(56),age_group_code  VARCHAR(56), gender  VARCHAR(56),file_date datetime,member_id  VARCHAR(56)')+');'  

    if object_id('tempdb..##temp_demographic_data_set') is not null drop table ##temp_demographic_data_set ;  
    execute sp_executesql @demographic_view;



    -- drop temporary table if already exist & create temporary table  
    if object_id('tempdb..#temp_state_megacity') is not null drop table #temp_state_megacity ;
    -- execute procedure to split comman seprated input value into rows   
    exec dbo.sp_split_string_into_rows @_state_megacity;
    -- get comma separated to row converted value into temporary table.
    select distinct  _stategroup_code.*,_stategroup.is_exposed into #temp_state_megacity from _stategroup_code
    INNER JOIN _stategroup on _stategroup_code.stategroup_id=_stategroup.id 
    where @templateid=0 OR
    _stategroup_code.stategroup_id in 
    (
        select split_data from  ##temp_convert 
        WHERE split_data in (SELECT master_id from master_template where master_type='STATE_GROUP' and (template_id=@templateid))
    );

    if object_id('tempdb..#temp_state_10lac') is not null drop table #temp_state_10lac ;   
    exec dbo.sp_split_string_into_rows @_state_10lac;
    select distinct  _stategroup_code.*,_stategroup.is_exposed   into #temp_state_10lac  from _stategroup_code 
    INNER JOIN _stategroup on _stategroup_code.stategroup_id=_stategroup.id
    where  @templateid=0 OR _stategroup_code.stategroup_id in 
    (
        select split_data from  ##temp_convert 
        WHERE split_data in (SELECT master_id from master_template where master_type='STATE_GROUP' and (template_id=@templateid))
    );

    if object_id('tempdb..#temp_state_below10') is not null drop table #temp_state_below10 ;   
    exec dbo.sp_split_string_into_rows @_state_below10;
    select distinct  _stategroup_code.*,_stategroup.is_exposed   into #temp_state_below10  from _stategroup_code 
    INNER JOIN _stategroup on _stategroup_code.stategroup_id=_stategroup.id
    where  @templateid=0 OR _stategroup_code.stategroup_id in 
    (
        select split_data from  ##temp_convert 
        WHERE split_data in (SELECT master_id from master_template where master_type='STATE_GROUP' and (template_id=@templateid))
    );

    if object_id('tempdb..#temp_state_rural') is not null drop table #temp_state_rural ;   
    exec dbo.sp_split_string_into_rows @_state_rural;
    select distinct _stategroup_code.*,_stategroup.is_exposed   into #temp_state_rural  
    from _stategroup_code
    INNER JOIN _stategroup on _stategroup_code.stategroup_id=_stategroup.id
    where  @templateid=0 OR _stategroup_code.stategroup_id in 
    (
        select split_data from  ##temp_convert 
        WHERE split_data in (SELECT master_id from master_template where master_type='STATE_GROUP' and (template_id=@templateid))
    );

    if object_id('tempdb..#temp_nccs') is not null drop table #temp_nccs;   
    exec dbo.sp_split_string_into_rows @_nccs;
    select distinct *  into #temp_nccs  from _nccs_code 
    where  @templateid=0 OR _nccs_code.nccs_id in 
    (
        select split_data from  ##temp_convert 
        WHERE split_data in (SELECT master_id from master_template where master_type='NCCS' and (template_id=@templateid))
    );


    if object_id('tempdb..#temp_gender') is not null drop table #temp_gender ;   
    exec dbo.sp_split_string_into_rows @_gender;
    select distinct * into #temp_gender from  ##temp_convert;

    if object_id('tempdb..#temp_agegroup') is not null drop table #temp_agegroup ;   
    exec dbo.sp_split_string_into_rows @_agegroup;
    select distinct *  into #temp_agegroup  from _agegroup_code 
    where  @templateid=0 OR _agegroup_code.agegroup_id in 
    (
        select split_data from  ##temp_convert 
        WHERE split_data in (SELECT master_id from master_template where master_type='AGE_GROUP' and template_id =@templateid)
    );
    if object_id('tempdb..#temp_demographic_data') is not null drop table #temp_demographic_data ;  


    SET @parameter_defination_list= ' @temp_startdate datetime,
                                        @temp_enddate datetime,
                                        @temp_Subscription_start datetime,
                                        @temp_Subscription_end datetime,

                                        @temp_is_nccs_exist varchar(56),
                                        @temp_is_agegroup_exist varchar(56),                                        
                                        @temp_is_gender_exist varchar(56)';






    -- Loading Data To Temporary table for the filters provided
    SET @temp_query= 
    'INSERT INTO  ##temp_demographic_data_set SELECT  '+@demographic_columns+'
    FROM dbo.raw_demographic_history 
    where  (file_date between cast(@temp_Subscription_start as datetime) and cast(@temp_Subscription_end as datetime)) and 
            (file_date between cast(@temp_startdate as datetime) and cast(@temp_enddate as datetime) ) 

            and
            (NOT @temp_is_nccs_exist=''true'' OR nccs_group in (select code from #temp_nccs) )and
            (NOT @temp_is_gender_exist=''true'' OR sex in (select * from #temp_gender) )and
            (NOT @temp_is_agegroup_exist=''true'' OR age_group in (select code from #temp_agegroup) );';


    execute sp_executesql @temp_query ,@parameter_defination_list, 
                             @temp_startdate=@_startdate ,
                             @temp_enddate=@_enddate,
                             @temp_Subscription_start=@_Subscription_start ,
                             @temp_Subscription_end=@_Subscription_end,

                             @temp_is_nccs_exist=@is_nccs_exist,
                             @temp_is_agegroup_exist=@is_agegroup_exist,                                        
                             @temp_is_gender_exist=is_gender_exist;




    -- Creating table with empty columns
     SELECT * INTO #temp_demographic_data FROM ##temp_demographic_data_set WHERE state_group_code=0;

    -- Create Indexes 
    CREATE INDEX IX_TEST_temp_demographic_data_1 ON #temp_demographic_data(nccs_group_code);
    CREATE INDEX IX_TEST_temp_demographic_data_2 ON #temp_demographic_data(town_class_code);
    CREATE INDEX IX_TEST_temp_demographic_data_3 ON #temp_demographic_data(age_group_code);
    CREATE INDEX IX_TEST_temp_demographic_data_4 ON #temp_demographic_data(state_group_code);
    CREATE INDEX IX_TEST_temp_demographic_data_5 ON #temp_demographic_data(member_id);
    CREATE INDEX IX_TEST_file_date_6 ON #temp_demographic_data(file_date);


    -- When All India IS True
    IF (@_state_megacity is null AND @_state_10lac is  null AND @_state_below10 is  null AND @_state_rural is  null) 
    BEGIN

        INSERT INTO #temp_demographic_data SELECT * FROM ##temp_demographic_data_set;

        -- SET Town class to empty
        /*
        UPDATE #temp_demographic_data
        SET town_class_code=NULL
        WHERE (state_group_code in (select code from #temp_state_megacity WHERE is_exposed=0)) OR
             (state_group_code in (select code from #temp_state_10lac WHERE is_exposed=0)) OR                               
             (state_group_code in (select code from #temp_state_below10 WHERE is_exposed=0)) OR
             (state_group_code in (select code from #temp_state_rural WHERE is_exposed=0)) ;            
        */
    END
    ELSE
    BEGIN

        -- WHEN All India is false and state,town columns exist in template
        IF @is_stategroup_exist=1 and @is_townclass_exist=1
        BEGIN

        -- Update Town class to empty for states not exposed to user

            INSERT INTO #temp_demographic_data

            SELECT * FROM 
            (
                SELECT * FROM ##temp_demographic_data_set
                WHERE 
                 (state_group_code in (select code from #temp_state_megacity WHERE is_exposed=0)) OR
                 (state_group_code in (select code from #temp_state_10lac WHERE is_exposed=0)) OR                               
                 (state_group_code in (select code from #temp_state_below10 WHERE is_exposed=0)) OR
                 (state_group_code in (select code from #temp_state_rural WHERE is_exposed=0))              
                UNION ALL
                SELECT * FROM ##temp_demographic_data_set
                WHERE 
                 (town_class_code=@_megacity and state_group_code in (select code from #temp_state_megacity WHERE is_exposed=1)) OR
                 (town_class_code=@_10lac and state_group_code in (select code from #temp_state_10lac WHERE is_exposed=1)) OR                               
                 (town_class_code=@_below10 and state_group_code in (select code from #temp_state_below10 WHERE is_exposed=1)) OR
                 (town_class_code=@_rural and state_group_code in (select code from #temp_state_rural WHERE is_exposed=1))              
            )demographic_data;


            /*UPDATE #temp_demographic_data
            SET town_class_code=NULL
            WHERE (state_group_code,town_class_code) in();
            */

        END

        -- When Only StateGroup Exists
        IF @is_stategroup_exist=1 and @is_townclass_exist=0
        BEGIN

            INSERT INTO #temp_demographic_data
            SELECT * FROM ##temp_demographic_data_set
            WHERE 
             (state_group_code in (select code from #temp_state_megacity )) OR
             (state_group_code in (select code from #temp_state_10lac )) OR                             
             (state_group_code in (select code from #temp_state_below10)) OR
             (state_group_code in (select code from #temp_state_rural)) ;   
        END

        -- When Only Town class Exists
        IF @is_stategroup_exist=0 and @is_townclass_exist=1
        BEGIN

            INSERT INTO #temp_demographic_data
            SELECT * FROM ##temp_demographic_data_set;
        END

        -- When State and Town Both Not exists
        IF @is_stategroup_exist=0 and @is_townclass_exist=0
        BEGIN

            INSERT INTO #temp_demographic_data
            SELECT * FROM ##temp_demographic_data_set;
        END     

    END



    -- select @temp_query;
    -- execute sp_executesql @temp_query;   


    if object_id('tempdb..##temp_report_data') is not null drop table ##temp_report_data ;
    -- if object_id('tempdb..##temp_report_data1') is not null drop table ##temp_report_data1   ;



    -- when input limit value is not null then 

    SET @temp_query='
    SELECT *,ROW_NUMBER() OVER (ORDER BY  file_date,member_id) as row_number INTO ##temp_report_data
    FROM 
    (
    SELECT DISTINCT file_date,member_id,'+@demographic_report+' 
    FROM
    (
    SELECT DISTINCT 
             coalesce(_nccs.name,'''')  as master_nccs,
            coalesce(_stategroup.name,'''') as master_state_group,
            coalesce(CASE WHEN _stategroup_code.is_exposed=0 THEN '''' ELSE _townclass.name END,'''')   as master_town_class,
            coalesce(_agegroup.name,'''') as master_age_group,  
            coalesce(CASE WHEN filetered_data.gender=1 THEN ''MALE'' ELSE ''FEMALE'' END,'''')as master_gender,
             -- cast(round(filetered_data.weight,2) as char(10)) as weight,filetered_data.household_id,
            filetered_data.*
        from( 
            select DISTINCT *
            from #temp_demographic_data
            )as filetered_data     
       left join #temp_nccs _nccs_code on filetered_data.nccs_group_code=_nccs_code.code 
       left join _nccs on _nccs_code.nccs_id=_nccs.id 
       left join _townclass on filetered_data.town_class_code=_townclass.code
       left join #temp_agegroup _agegroup_code on filetered_data.age_group_code=_agegroup_code.code
       left join _agegroup on _agegroup_code.agegroup_id=_agegroup.id
       left join (     
            select * from #temp_state_megacity
            union 
            select * from #temp_state_10lac
            union 
            select * from #temp_state_below10
            union 
            select * from #temp_state_rural
       )_stategroup_code on filetered_data.state_group_code=_stategroup_code.code
       left join _stategroup on _stategroup_code.stategroup_id=_stategroup.id
     )report  

    ) report_view';


    execute sp_executesql @temp_query ;
    CREATE INDEX IX_TEST_temp_temp_report_data ON ##temp_report_data(row_number);


    SET @record_count=(SELECT max(row_number) FROM ##temp_report_data);

    IF @limt is not null 
    begin       
        select *,@record_count as count FROM ##temp_report_data
        where row_number between @off_set and @off_set+@limt; 
    end
    -- when input limit value is null
    else 
    begin

        select *,@record_count as count FROM ##temp_report_data 
    END 

     -- Drop temporary tables 
    if object_id('tempdb..#temp_demographic_data') is not null drop table #temp_demographic_data ;  
    if object_id('tempdb..##temp_demographic_data_set') is not null drop table ##temp_demographic_data_set ;
    if object_id('tempdb..#temp_agegroup') is not null drop table #temp_agegroup ;
    if object_id('tempdb..#temp_gender') is not null drop table #temp_gender ; 
    if object_id('tempdb..#temp_nccs') is not null drop table #temp_nccs; 
    if object_id('tempdb..#temp_state_rural') is not null drop table #temp_state_rural ; 
    if object_id('tempdb..#temp_state_below10') is not null drop table #temp_state_below10 ; 
    if object_id('tempdb..#temp_state_10lac') is not null drop table #temp_state_10lac ; 
    if object_id('tempdb..#temp_state_megacity') is not null drop table #temp_state_megacity ;

   END

C# Code :

  SqlParameter[] mySqlParam = new SqlParameter[19];
                mySqlParam[0] = new SqlParameter("_startdate", startDate);
                mySqlParam[1] = new SqlParameter("_enddate", endOfDay);
                mySqlParam[2] = new SqlParameter("_nccs", nccs == "" ? null : nccs);
                mySqlParam[3] = new SqlParameter("_megacity", Convert.ToInt32(ConfigurationManager.AppSettings["megacities"]));
                mySqlParam[4] = new SqlParameter("_10lac", Convert.ToInt32(ConfigurationManager.AppSettings["tento75L"]));
                mySqlParam[5] = new SqlParameter("_below10", Convert.ToInt32(ConfigurationManager.AppSettings["urban"]));
                mySqlParam[6] = new SqlParameter("_rural", Convert.ToInt32(ConfigurationManager.AppSettings["rural"]));
                mySqlParam[7] = new SqlParameter("_state_megacity", megaCities == "" ? null : megaCities);
                mySqlParam[8] = new SqlParameter("_state_10lac", tenTo75Lac == "" ? null : tenTo75Lac);
                mySqlParam[9] = new SqlParameter("_state_below10", urban == "" ? null : urban);
                mySqlParam[10] = new SqlParameter("_state_rural", rural == "" ? null : rural);

                string gender = null;
                if (male == true && female == true)
                {
                    gender = ConfigurationManager.AppSettings["male"] + "," + ConfigurationManager.AppSettings["female"];
                }
                else if (male == true)
                {
                    gender = ConfigurationManager.AppSettings["male"];
                }
                else if (female == true)
                {
                    gender = ConfigurationManager.AppSettings["female"];
                }


                mySqlParam[11] = new SqlParameter("_gender", gender);
                mySqlParam[12] = new SqlParameter("_agegroup", ageGroup == "" ? null : ageGroup);
                mySqlParam[13] = new SqlParameter("off_set", offSet);
                mySqlParam[14] = new SqlParameter("limt", limit);
                mySqlParam[15] = new SqlParameter("_Subscription_start", subscription_start);
                mySqlParam[16] = new SqlParameter("_Subscription_end", endOfDaySubscription);
                mySqlParam[17] = new SqlParameter("_demographic_field_id ", demographicFields);
                mySqlParam[18] = new SqlParameter("templateid ", templateId);
                DataSet ds = new DataSet();
                ds = SqlHelper.ExecuteDataset(GlobalConstants.ConnString, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_demographic_history_csv"].ToString(), mySqlParam);
                return ds;

 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            //create & open an SqlConnection, and dispose of it after we are done.
            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                cn.Open();

                //call the overload that takes a connection in place of the connection string
                return ExecuteDataset(cn, commandType, commandText, commandParameters);
            }
        }

Answer 1

Is it possible that, in production, there're instances where the script returns no rows?

select @demographic_report= {...}
from dbo.demgraphic_fields
where is_inventory_field=0 and ( @templateid=0 OR id in (select cast(split_data as int) from  ##temp_convert));

If this is the case, @demographic_report remains null, 'any string' + null is null, sp_executesql is executed against a null string that does nothing, but the table is not created and this causes your error.