TSQL Define Temp Table (or table variable) Without Defining Schema?

Question

Is there a way to define a temp table without defining it's schema up front?

Answer 1

Actually using a table VARIABLE, an in-memory table, is the optimal way to go. The #table creates a table in temp db, and ##table is global - both with disk hits. Consider the slow-down/hit experienced with the number of transactions.

CREATE PROCEDURE [dbo].[GetAccounts]      @AccountID BIGINT,     @Result INT OUT,     @ErrorMessage VARCHAR(255) OUT AS BEGIN     SET NOCOUNT ON;     SET @Result = 0     SET @ErrorMessage = ''      DECLARE @tmp_Accounts TABLE (                                                 AccountId BIGINT, AccountName VARCHAR(50), ... )  INSERT INTO @tmp_Accounts ([AccountId], [AccountName]... ) SELECT AccountID, AccountName FROM Accounts WHERE  ...       IF @@Rowcount = 0         BEGIN             SET @ErrorMessage = 'No accounts found.'             SET @Result = 0              RETURN @Result         END     ELSE         BEGIN             SET @Result = 1              SELECT *             FROM @tmp_Accounts         END  

Note the way you insert into this temp table.

The down-side of this is that it may take a bit longer to write, as you have to define your table variable.

I'd also recommend SQL Prompt for Query Analyzer by RedGate.