0

I have to insert execution of stored procedure into table. For example, I have procedure called proc and I want execute it and insert its data into table. I found some example like this:

DECLARE @storedProcname NVARCHAR(MAX) = ''
SET @storedProcname = 'proc'
DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '
SELECT @strSQL = @strSQL+STUFF((
SELECT ',' + name +' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storedProcname),0)
FOR XML PATH('')),1,1,'(') + ')'
print @strSQL
EXEC (@strSQL)
INSERT INTO myTableName
EXEC ('proc')
SELECT * FROM myTableName
--DROP TABLE myTableName

It works for THIS procedure but how to create for each procedure its own table for inserting data? Because I have a lot of procedures and they all have different amount of columns and datatype of columns is different too. I imagine I'll write some cursor, where the script will list all of procedures, create tables for them and insert them into tables or something else.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Kirill Galeev
  • 1
  • 1
  • 6
    The table you're inserting into has to match the schema of the stored procedure's first result set, so you'll likely need a separate table for each stored procedure. Stored procedures that return multiple result sets cannot be used in this manner. – AlwaysLearning Mar 10 '21 at 09:36
  • Does this answer your question? [Insert results of a stored procedure into a temporary table](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) – Panagiotis Kanavos Mar 10 '21 at 09:42
  • You can use `SELECT * INTO myTableName FROM OPENROWSET(...)` to create a table from the results. Such a table will need modifications though, to add proper keys and indexes at the very least, probably to adjust column sizes as well – Panagiotis Kanavos Mar 10 '21 at 09:46
  • 1
    Why do you want the result in a table, what are you trying to achieve? By the way the code you have is not quite right, missing `(` after `myTableName` on line 3. It also doesn't take into account `varchar` lengths and collation, `decimal` precision, and not null. It also is not ordering by `column_ordinal`. And `dm_exec_describe_first_result_set_for_object` doesn't take into account `if` and `while` statements – Charlieface Mar 10 '21 at 10:21
  • Yes - smells like a big [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – SMor Mar 10 '21 at 13:10

0 Answers0