SQL Server Performance

Converting Temp Table usage to Table Data Type

Discussion in 'General Developer Questions' started by bergshawn, Nov 5, 2002.

  1. bergshawn New Member

    After reading your newsletter today and finding out about the Table data type, I am attempting to convert one of my most used stored procedures to use the table data type instead of temp tables. One of the things the stored procedure does is dynamically generate an insert statement to insert data in the the current temp table. This works fine. However, I cannot seem to find a way to perform this same task using a temp table, since when I call the EXEC on the dynamically generated SQL it has no idea of the table variable I am referencing in the INSERT statement. Any help would be appreciated. How could I get around this? I do not want to have to program a complete different CASE for each unique set of variables that can be passed into the stored procedure. This would make for a very long and ugly stored procedure.

    Thanks for the help in advance. And congrats on the new forum! I hope to be on here quite often.
  2. Lana New Member


    "Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

    INSERT INTO table_variable EXEC stored_procedure

    SELECT select_list INTO table_variable statements."

    Wouldn't it be nice if you could use it with a dynamic query? The majority of temp tables in my procedures are created because of the changing search criteria.
    Though, you could try rewrite your selects to use isnull or case instead, ex.:

    SELECT * from table where isnull(column,0)=isnull(@column,isnull(column,0)) and so on

  3. bergshawn New Member

    Yes I also saw that in the BOL. Was just wondering how I could work-around it. The problem is we allow our users to change the sorting and a few other options for each query and we pass in dynamically the ORDER BY clause and sometimes the WHERE clause, thus dynamic sql is required. There has to be a better way to accomplish this.

  4. Lana New Member

    order by is not a problem either

    select * table order by case @col1 when 'col1' then col1
    when 'col2' then col2 when 'col3' then col3 end

  5. bergshawn New Member

    The problem with specifying the ORDER BY in a "CASE" is that you have to have the order (ASC or DESC) outside of the case, and it cannot be based on the value passed (which will not work). I will also have to have a dynamic HAVING clause.

Share This Page