14 Comments

1. joy says:

   January 21, 2012 at 1:58 pm

   how make a procedure to insert the values where one column is the foregin key of the primary key in a single table
   like

   create table test1(id int identity constraint mno1 primary key ,name varchar(100), sno int constraint constraint_test1_id_fk references test1(id))

   Reply to this comment
   • Derek Dieter says:

     January 23, 2012 at 10:05 am

     Hi Joy,

     You can use this.

     http://sqlserverplanet.com/sql-server-2008/using-the-merge-statements-output-clause-to-match-inserted-identity-fields-with-source-table-fields

     Derek

     Reply to this comment
2. farid says:

   January 11, 2012 at 9:41 am

   How produce present store procedure from tables?

   Reply to this comment
3. rmotiani says:

   November 22, 2011 at 9:09 am

   How to pass a table variable in OpenRowSet?

   Reply to this comment
4. Vince says:

   September 18, 2011 at 6:52 am

   “Retrieving the result set from a stored procedure has historically proven to be a difficult task.”

   Uhh… why not just:

   INSERT INTO #TABLE
   EXEC sp_name

   or if you need specific columns
   INSERT INTO #TABLE
   (ColumnName1, ColumnName2)
   EXEC sp_name

   Am I missing the true point of this post? It doesn’t seem very a very difficult task to me.

   Reply to this comment
   • Derek Dieter says:

     September 19, 2011 at 10:01 pm

     Hi Vince,

     The problem I’ve always faced is not knowing what the column names or data types is of the procedure you are executing in advance. These are needed in order to create the temp table definition. The ultimate solution is to create the temp table dynamically based on the result set of the procedure at execution time. That’s the scenario this article addresses. Does that make sense or am I missing something also?

     Reply to this comment
5. Sandeep says:

   May 12, 2011 at 3:19 pm

   Hi,

   Everything works fine. But I am facing problem when Sproc outputs a XML result set.

   For Example,
   Output of
   EXEC DB.schema.ProcName

   Result set is

   4F7135AF-03C2-4166-9645-A81EED3656AC
   false
   0

   {NONE}

   However when i tried

   SELECT *
   FROM OPENQUERY([LOCALSERVER], ‘EXEC DB.schema.ProcName’)

   Result set is something like this below. which is in binary or image format.

   0x44065400690063006B006500740044085400690063006B006500740049004400440E53006F00750072006300650045006E00640070006F0069006E0074004413440065007300740069006E006100740069006F006E0045006E00640070006F0069006E0074004409500072006F0063006500730073004900440044114F0072006900670069006E0061006C004D00650073007300610067006500490040000040074727565810C010D410310E4040000050066616C7365810D010E410310E4040000050066616C7365810E010F410310E4040000010030810F0110410111410112410310E404000006007B4E4F4E457D8112811181108101

   How do I proceed from here.?

   Reply to this comment
6. Anonymous says:

   March 14, 2011 at 2:58 am

   koloko hmer

   Reply to this comment
7. Stephen says:

   October 7, 2010 at 11:20 am

   So, how do I take a stored procedure that has multiple selects and take the results of those selects and put them into different tables? Any help would be greatly appreciated. Thanks!

   Reply to this comment
   • Derek Dieter says:

     October 13, 2010 at 10:42 pm

     Hi Stephen,

     You can’t do that unfortunately. While this article does show how to take the result of a stored procedure and capture the result, it’s really a frowned upon way to do things. Ultimately, procedures should not return multiple result sets. When creating a procedure, the goal should be to make it as atomic as possible. Meaning it should only serve one purpose.

     Reply to this comment
8. unruledboy says:

   July 5, 2010 at 6:53 pm

   how to pass a temp table from one sp to another sp without the table name and exec sql method?

   I mean can we pass the temp table like:

   CREATE PROCEDURE TestTablePara2
   @TempTable TABLE
   AS

   Reply to this comment
9. Coco says:

   April 11, 2010 at 7:20 pm

   Sick man! I have to document a full db of sprocs (fun) and this’ll do the trick nicely for getting some things that’ll me go

   Reply to this comment
   • Derek D. says:

     April 11, 2010 at 8:11 pm

     Great! This is pretty useful. It would also be nice if SQL Server would officially support the insertion of a result set into a table without having to do workarounds.

     Reply to this comment
10. Create Local Linked Server for OPENQUERY says:

    June 18, 2009 at 10:32 pm

    [...] For that example, follow this link: (Insert Results of Stored Procedure Into Table) [...]

    Reply to this comment