locked
Is there any way to dynamically create a table variable without static field declarations? RRS feed

  • Question

  • (I'm fairly sure the answer is no but I need to be sure.)

    Assuming a stored procedure returning a table, is there any way to assign that table to a local table variable in a script without first declaring the internal structure of the table variable in the usual way?  (We're using SQL 2012.)

    Wednesday, May 4, 2016 7:41 PM

Answers

  • Generally speaking, the answer is no.  Just as you cannot use a select statement to retrieve rows from a stored procedure, you cannot use the select...into version to store the resultset into a table.  There is a hack (IMO) that is prone to issues/errors:  select/into/stored procedure

    You CAN insert into a table using an insert statement and executing a stored procedure (documented in BOL, so now is a good time to go review the information).  But of course, the table must be created prior to executing the insert statement.

    • Proposed as answer by Naomi N Wednesday, May 4, 2016 8:56 PM
    • Marked as answer by Eric__Zhang Tuesday, May 10, 2016 1:53 AM
    Wednesday, May 4, 2016 8:32 PM
  • I have to ask:  What do you plan on doing with said table if you don't know its structure???  You can't insert any rows, because you don't know the columns or data types to insert.  As far as SELECT, you could only SELECT * (which is bad coding practice).  UPDATEs and DELETEs are equally problematic.

    If there's really such a need, you'd have to describe your situation in far more detail.  I realize you don't want to give away company secrets, but you need to give enough information for us to understand what you're trying to accomplish.  Try to think of a "parallel" scenario where the need would be useful and use that as your requirement...  If your application is insurance, how might the requirement be needed in manufacturing or sales?  If you're in dental insurance, how might it apply to general medical insurance?  You get the idea...

    One last thought:  Would dynamic SQL help?

    HTH,

    Carl

    • Proposed as answer by Eric__Zhang Thursday, May 5, 2016 1:39 AM
    • Marked as answer by Eric__Zhang Tuesday, May 10, 2016 1:53 AM
    Wednesday, May 4, 2016 11:39 PM

All replies

  • Nope.

    You can do it with a temp table though, using the INTO clause.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, May 4, 2016 7:44 PM
  • Thanks for the prompt response but I'm not sure what you are referring to.

    Keep in mind that what I'd like to do is have the return from the stored proc create the structure of the table variable.

    Wednesday, May 4, 2016 7:51 PM
  • You cannot create a table variable without specifying the structure.

    You CAN however, do this with a temp table. by making use of the INTO clause:

    SELECT *
     INTO #myTempTable
      FROM sometable


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, May 4, 2016 7:57 PM
  • That still doesn't work.  Remember I need to use a stored proc.

    SELECT * INTO #myTempTable from exec test1

    -------------- 

    On the other hand I was able to get this idea to work with a function.

    SELECT *
     INTO #myTempTable
     from test1f()
     
      select * from #myTempTable

      drop table #myTempTable

    • Edited by B. Chernick Wednesday, May 4, 2016 8:27 PM clarity
    Wednesday, May 4, 2016 8:19 PM
  • Generally speaking, the answer is no.  Just as you cannot use a select statement to retrieve rows from a stored procedure, you cannot use the select...into version to store the resultset into a table.  There is a hack (IMO) that is prone to issues/errors:  select/into/stored procedure

    You CAN insert into a table using an insert statement and executing a stored procedure (documented in BOL, so now is a good time to go review the information).  But of course, the table must be created prior to executing the insert statement.

    • Proposed as answer by Naomi N Wednesday, May 4, 2016 8:56 PM
    • Marked as answer by Eric__Zhang Tuesday, May 10, 2016 1:53 AM
    Wednesday, May 4, 2016 8:32 PM
  • Er, if you insert into the temp table INSIDE the proc, it'll be no problem at all..

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, May 4, 2016 8:52 PM
  • I have to ask:  What do you plan on doing with said table if you don't know its structure???  You can't insert any rows, because you don't know the columns or data types to insert.  As far as SELECT, you could only SELECT * (which is bad coding practice).  UPDATEs and DELETEs are equally problematic.

    If there's really such a need, you'd have to describe your situation in far more detail.  I realize you don't want to give away company secrets, but you need to give enough information for us to understand what you're trying to accomplish.  Try to think of a "parallel" scenario where the need would be useful and use that as your requirement...  If your application is insurance, how might the requirement be needed in manufacturing or sales?  If you're in dental insurance, how might it apply to general medical insurance?  You get the idea...

    One last thought:  Would dynamic SQL help?

    HTH,

    Carl

    • Proposed as answer by Eric__Zhang Thursday, May 5, 2016 1:39 AM
    • Marked as answer by Eric__Zhang Tuesday, May 10, 2016 1:53 AM
    Wednesday, May 4, 2016 11:39 PM
  • Possibly, but I'd still have to figure out what to put in the string. 

    My specs are not well defined at the moment and I'm looking at alternatives.  The basic problem seems to be pivoting a large and variable number of fields, pivoting (or cross-tabbing if you like) without aggregating.  And since I rarely do this sort of thing I need to review and research.

    Thursday, May 5, 2016 12:57 PM
  • Ya can't solve a problem without knowing what the problem is!  :-) :-) :-)
    Saturday, May 7, 2016 9:32 PM

  • >>  a stored procedure returning a table, is there any way to assign that table to a local table variable in a script without first declaring the internal structure of the table variable <<

    This is a fundamentally bad question. A stored procedure should return a table as a result, in the scenario. A table is a set of things, not a variable in the sense of the old procedural language are still trying to think in. What you wanted to do in your old mindset was having a scratch tape, write the data to the scratch tape and then use that tape, like you did in 1970's COBOL!

    This is exactly what another poster told you to do with the temp table (fake scratch tape).

    Since you posted no DDL or anything else (this is always bad), I will guess that your problem could probably be solved with a view that is always correct the time of execution and that you probably do not need a stored procedure. New SQL programmers coming to a declarative language do not understand the concept of dynamic data, so they materialize the results (a.k.a. fake scratch tapes) instead of using an expression. But you can tell us anything, so I am only guessing. Wow! Sure wish you it helped us so we could help you :-(

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, May 8, 2016 1:11 AM
  • Oh! Oh! I almost forgot to tell you that fields are not columns! This is so fundamentally stupid, it is usually covered in the first week of any decent SQL class. SQL tables are based on columns, not fields. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, May 8, 2016 1:13 AM
  • My apologies for this vague (but interesting) discussion. 

    As it turns out, we've decided, for now anyway, to forget about temp tables/variables and go with creating a single query string and using Exec.  

    Thanks one and all.

    Monday, May 9, 2016 2:49 PM