nested stored procedure

• mpradeep23

  SSCommitted

  Points: 1868

  More actions

  January 2, 2015 at 11:16 pm

  #294046

  hi all

  i have a stored proc which will return two result sets

  i am call the above stored proc in other stored proc to store both values into table

  can any one let me know i can i do it i have no idea regrading this

  thanks,

  pradeep

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  January 2, 2015 at 11:28 pm

  #1767519

  No, you can't use two result sets from a single procedure to insert data into a table.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  January 3, 2015 at 1:40 pm

  #1767575

  Quick thought, this can be done in some circumstances where the result sets are equal in structure, consider this example

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  GO

  CREATE PROCEDURE dbo.TWO_RESULT_USP

  AS

  SELECT

  OBJECT_ID

  ,name

  FROM sys.ObjectS WHERE OBJECT_ID > 100

  SELECT

  OBJECT_ID

  ,name

  FROM sys.ObjectS WHERE OBJECT_ID <= 100

  GO

  DECLARE @RES TABLE (OBJECT_ID INT NOT NULL,ONAME SYSNAME NULL);

  INSERT INTO @RES(OBJECT_ID,ONAME)

  EXEC dbo.TWO_RESULT_USP;

  SELECT * FROM @RES

  DROP PROCEDURE dbo.TWO_RESULT_USP;

  Results (Shortened)

  OBJECT_ID ONAME

  ----------- ----------------------------------

  101575400 sqlagent_jobs

  117575457 sqlagent_jobsteps

  133575514 sqlagent_job_history

  149575571 sqlagent_jobsteps_logs

  245575913 service_broker_map

  261575970 fulltext_thesaurus_metadata_table

  277576027 fulltext_thesaurus_state_table

  293576084 fulltext_thesaurus_phrase_table

  -1441179818 #AA195756

  3 sysrscols

  5 sysrowsets

  6 sysclones

  7 sysallocunits

  8 sysfiles1

  9 sysseobjvalues

  17 syspriorities

  18 sysdbfrag

  19 sysfgfrag

  20 sysdbfiles