Search code examples
sql-serverstored-procedurestemp-tables

How to Auto Generate Code for Stored Procedure Column Data Types - SQL Server

My desired end result is to simply be able to SELECT from a Stored Procedure. I've searched the Internet and unfortunately the Internet said this can't be done and that you first need to create a Temp Table to store the data. My problem is that you must first define the columns in the Temp Table before Executing the STORED Procedure. This is just time consuming. I simply want to take the data from the stored procedure and just stick it into a Temp Table.

What is the FASTEST route to achieve this from a coding perspective? To put it simply it's time consuming to first have to lookup the returned fields from a Stored Procedure and then write them all out.

Is there some sort of tool that can just build the CREATE Table Statement based on the Stored Procedure? See screenshot for clarification.

enter image description here

Most of the Stored Procedures I'm dealing with have 50+ fields. I don't look forward to defining each of these fields manually.

Here is good SO Post that got me this far but not what I was hoping. This still takes too much time. What are experienced SQL Server guys doing? I've only just recently made the jump from Oracle to SQL Server and I see that Temp Tables are a big deal in SQL Server from what I can tell.

Solution

  • If you don't mind ##temp table and some dynamic SQL

    NOTE: As Luis Cazares correctly pointed out... the ##temp runs the risk of collision due to concurrency concerns

    Example

    Declare @SQL  varchar(max) = 'Exec [dbo].[prc-App-Lottery-Search] ''8117'''
Declare @temp varchar(500) = '##myTempTable'

Set @SQL = '
If Object_ID(''tempdb..'+@temp+''') Is Not NULL Drop Table '+@temp+';   
Create Table '+@temp+' ('+stuff((Select concat(',',quotename(Name),' ',system_type_name) 
                                  From  sys.dm_exec_describe_first_result_set(@SQL,null,null )  A 
                                  Order By column_ordinal
                                  For XML Path ('')),1,1,'') +') 

Insert '+@temp+' '+@SQL+'
'
Exec(@SQL)

Select * from ##myTempTable