Get Stored Procedure Result to A Table


  

--USE yourDB 
--GO
----Get Stored procedure result into a table

;with mycte as (
SELECT distinct p.name, r.column_ordinal,r.name colName,r.system_type_name,r.is_nullable 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
WHERE p.name not like'sp_%' --no system sp
and p.name ='getYourSpName'  
)

 

  SELECT   N'If Object_id('''+QUOTENAME('Show_SpResult_'+m1.name)+''',''U'') is not null DROP Table '
  + QUOTENAME('Show_SpResult_'+m1.name) 
  + ' Create table '
  + QUOTENAME('Show_SpResult_'+m1.name) 
  +'('+ STUFF((SELECT  ', ' + QUOTENAME(Colname) + ' ' 
  + system_type_name + Case when is_nullable=1 then  ' NULL' Else ' Not NULL' END
	  FROM mycte AS m2
	  WHERE m1.name=m2.name
	  ORDER BY m2.column_ordinal
	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
	  +');  INSERT INTO '+QUOTENAME('Show_SpResult_'+m1.name)+ ' EXEC '+ Name 
	  + ';  SELECT * FROM '+QUOTENAME('Show_SpResult_'+m1.name) +';' as QueryToBeExecuted
 FROM mycte AS m1
 Group by name

 --Copy the result and execute to get the result (you need to pass in parameters' values if your sp has them )
 --We can make this script to run dynamically

 
 --More information about sys.dm_exec_describe_first_result_set_for_object 
 -- https://msdn.microsoft.com/en-us/library/ff878236%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s