Search code examples
stored-proceduressql-server-2000

Declare a table variable without column definitions?


Is there a way, in SQL Server, to declare a table variable without knowing the table definitions?

Exempli gratia:

DECLARE @Results TABLE
INSERT INTO @Results EXEC MyProc @param1 = @myValue

or

DECLARE @Results TABLE
SELECT INTO @Results EXEC MyProc @param1 = @myValue

or

DECLARE @Results TABLE
EXEC MyProc @param1 = @myValue INTO @Results

or

DECLARE @Results TABLE
EXEC INTO @Results MyProc @param1 = @myValue

or

DECLARE @Results TABLE
SELECT * FROM EXEC MyProc @param1 = @myValue INTO @Results

or

DECLARE @Results TABLE
SELECT * INTO @Results FROM EXEC MyProc @param1 = @myValue

or

DECLARE @Results TABLE
SELECT * INTO @Results EXEC MyProc @param1 = @myValue

(you get the idea)


Solution

  • Impossible. Citation from "books online":

    ==============

    Syntax Note Use DECLARE @local_variable to declare variables of type table.

    table_type_definition ::= 
      TABLE ( { column_definition | table_constraint } [ ,...n ] ) 
    

    ==============

    "(", at least one column definition and ")" is syntactically required.

    PS: AFAIK insertion into any new table from "exec" results are impossible at all. Only to a table with predefined structre.