InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 01:30:22 GMT20http://www.sqlservercentral.com/Forums/Topic1385052-391-1.aspxLowell beat me to it...use temp tables.Stored Procedure 3 calls Stored Procedure 1 which puts the results1 into #TempTableStored Procedure 3 calls Stored Procedure 2 which puts the results2 into #TempTableSince Query 1 & 2 have the same structure you only need one temp table.Finally you just "SELECT * FROM #TempTable WHERE blah = foo" replacing the * with whatever desired columns you need and adding whatever filtering is necessary.A sample template should give you the idea:[code="sql"]IF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable ( [ID] INT IDENTITY(1,1) NOT NULL, [Col1] INT NULL, [Col2] NVARCHAR(50) NULL, PRIMARY KEY (ID), UNIQUE (ID))INSERT INTO #TempTableEXEC dbo.myProc1 -- the rows being inserted must match those of #TempTableINSERT INTO #TempTableEXEC dbo.myProc2SELECT ID ,Col1 ,Col2FROM #TempTable[/code]Fri, 16 Nov 2012 12:01:09 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1385052-391-1.aspxanother , better possibility os to create temp tables to hold the results of the two procs, and then use multiple temp tables:the only issue with that is knowing the layout of the results of the procedure.[code] CREATE TABLE #who ( [#whoID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [SPID] CHAR (5) NULL, [INSERTDT] DATETIME NULL DEFAULT(GETDATE()), [STATUS] VARCHAR(30) NULL, [LOGIN] VARCHAR(30) NULL, [HOSTNAME] VARCHAR(30) NULL, [BLKBY] VARCHAR(30) NULL, [DBNAME] VARCHAR(30) NULL, [COMMAND] VARCHAR(30) NULL, [CPUTIME] INT NULL, [DISKIO] INT NULL, [LASTBATCH] VARCHAR(30) NULL, [PROGRAMNAME] VARCHAR(200) NULL, [SPIDINT] INT NULL ) --table exists, insert some data INSERT INTO #who(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT) EXEC sp_who2 --don't care about spids less than 50 anyway: DELETE FROM #who WHERE SPIDINT < 50END[/code]Thu, 15 Nov 2012 06:09:59 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1385052-391-1.aspx[quote][b]sumith1andonly1 (11/15/2012)[/b][hr]i have the following:Stored Procedure 1 calls Query 1Stored Procedure 2 calls Query 2i would like to have:Stored Procedure 3 calling Query 1 and Query 2 and then combine results.Query 1 is to select data from table1Query 2 is to select data from table2Both table1 and table2 have same structure..My actual need is combine data from Query 1 and data from Query2 ,and return single data[/quote]details..we need details.while it's [i]technically possible[/i] to use OPENROWSET to treat the results of a procedure as a table, it's not practical.it's so not practical, i hesitate to even show it, but an example is at the end.the right thing to do is create a brand new procedure, and copy and paste the work that occurs in Procedure 1 and Procedure 2 to get you the final results you need. that means you do not NOT CALL the other procs...you create a new procedure to satisfy this new business need.obviously this new procedure would need all the parameters passed to both those other two procs.[code]SELECT *FROM OPENROWSET( 'SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'SET FMTONLY OFF; SET NOCOUNT ON; exec Db_Name.dbo.spGetCategoriesByDocIDAsTable 811' )[/code]Thu, 15 Nov 2012 06:04:29 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1385052-391-1.aspxi have the following:Stored Procedure 1 calls Query 1Stored Procedure 2 calls Query 2i would like to have:Stored Procedure 3 calling Query 1 and Query 2 and then combine results.Query 1 is to select data from table1Query 2 is to select data from table2Both table1 and table2 have same structure..My actual need is combine data from Query 1 and data from Query2 ,and return single dataThu, 15 Nov 2012 04:19:19 GMTsumith1andonly1http://www.sqlservercentral.com/Forums/Topic1385052-391-1.aspxjust a thought, why are they inside procedures and not just a view?Could you rewrite them as table functions, in which case you can use CROSS APPLYThu, 15 Nov 2012 03:55:26 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1385052-391-1.aspxI don't understand your query - would you mind adding some code you have so we can have a wee look at it? that would help tremendously.Just based on what you wrote and me guessing what you are trying to achieve, you have the following:Stored Procedure 1 calls Query 1Stored Procedure 2 calls Query 2You would like to have:Stored Procedure 3 calling Query 1 and Query 2 and then combine (?) results.You can call as many queries (SELECT statements) as you want in a SP - what you do with it is up to you, but - generally speaking - if you run a query and need the results of that query to be used further down the SP, then you would 'save' the results of the first query in a temp table or a table variable (or a "real" table) - and then use that as input for other queries.Again, provide the code for your SPs (the relevant bits) and your query and spell out what you want to achieve (ie. how/why you want to combine the queries).BThu, 15 Nov 2012 03:54:31 GMTbleroyhttp://www.sqlservercentral.com/Forums/Topic1385052-391-1.aspxWhen you say combined, do you mean that they have the same structure and need to be seen as a single results set in which case you can useUNIONor UNION ALL (if there are potential duplicates between the results sets which you want to eliminate)Or do you mean that you need to JOIN the tables on a common field in which case you will need to capture the results sets into temp tables and work with them there.Thu, 15 Nov 2012 03:54:17 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1385052-391-1.aspxI have problem :I have to 2 select queries , which is called inside 2 separate procedures.But i have to call both queries inside a single procedure and result of two queries need to be combined. is this done with temporary table inside procedure?Or Any other solution??Thu, 15 Nov 2012 03:42:15 GMTsumith1andonly1