locked
Present Data in Presentation Layer after Removing the Temp Table RRS feed

  • Question

  • Inside of stored procedure you have the code

    create table #datatable (id int,
                            name varchar(100),
                            email varchar(10),
                            phone varchar(10),
                            cellphone varchar(10),
                            none varchar(10)                        
                           );
    
    insert into #datatable
    exec ('select *
    from datatable
    where id = 1') 
    
    select * from #datatable

    I still want to retrieve the data and present it in the software's presentation layer but I still want to remove the temp table in order to reduce performance issue etc.

    If I paste the code DROP TABLE #datatable after

    insert into #datatable
    exec ('select *
    from datatable
    where id = 1') 

    Does it gonna work to present the data in the presentation layer after removing the temp table?

    The goal is to retrieve the data from a stored procedure and present it in the software's presentation layer and I still need to remove the temptable after using it because I will use the SP many time in production phase.

    Thanks!

    http://sqlfiddle.com/#!3/14bbc/1/1

    Thursday, May 21, 2015 8:19 AM

Answers

  • Answer to your question is, Yes ! But wait ! Why you want to do like this? If you are dealing with data-warehousethen queries like this can fill up your tempdb.

    Approach 1 : You can simply write your select statement and stored procedure will return expected column.

    Approach 2: If you have complex query and #datatable is used to have intermediate result then I will suggest you to use sp_executesql instead of exec. 

    Approach 3: It may be like that you are building your query on front end, then I suggest you to execute it from there.

     In any case, if you are using dynamic sql and then consider reading The curse and blessing of dynamic SQL by Erland Sommarskog.

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    • Marked as answer by Eric__Zhang Tuesday, June 2, 2015 3:20 AM
    Thursday, May 21, 2015 10:13 AM

All replies

  • hmm, as long as there is no more context..

    I don't see any reason for using dynamic approaches, so using a temp table makes no sense at all in this scenario.

    Why don't you simply use select * from datatable where id = 1 directly?

    Thursday, May 21, 2015 8:54 AM
  • Well we see a scope of optimization in your code, however to answer you YES, the presentation layer will get the result of the selection (As a data set) and then your would anyway drop this table.

    As Stefan suggested, why would want to use dynamic SQL when you can query it directly from base tables ?


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Thursday, May 21, 2015 8:58 AM
  • I assume you have much more complex dynamic query and multiple steps that deals with a temp table. Being said that the temp tables will be removed as soon as they go out of scope. You may not get additional performance benefit by adding drop table statements in your code(though it would be a good practice).

    Satheesh
    My Blog | How to ask questions in technical forum

    Thursday, May 21, 2015 9:15 AM
  • Hello

    There is no point in creating a temp table for a simple select statement such as this.  why not just use the select Statement?


    Please click "Mark As Answer" if my post helped. Tony C.

    Thursday, May 21, 2015 9:50 AM
  • Answer to your question is, Yes ! But wait ! Why you want to do like this? If you are dealing with data-warehousethen queries like this can fill up your tempdb.

    Approach 1 : You can simply write your select statement and stored procedure will return expected column.

    Approach 2: If you have complex query and #datatable is used to have intermediate result then I will suggest you to use sp_executesql instead of exec. 

    Approach 3: It may be like that you are building your query on front end, then I suggest you to execute it from there.

     In any case, if you are using dynamic sql and then consider reading The curse and blessing of dynamic SQL by Erland Sommarskog.

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    • Marked as answer by Eric__Zhang Tuesday, June 2, 2015 3:20 AM
    Thursday, May 21, 2015 10:13 AM