Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

combining result in stored procedure ?? Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 3:42 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:48 AM
Points: 76, Visits: 346
I 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??
Post #1385052
Posted Thursday, November 15, 2012 3:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 461, Visits: 693
When 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 use

UNION

or

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.

Post #1385063
Posted Thursday, November 15, 2012 3:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:22 AM
Points: 167, Visits: 691
I 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 1
Stored Procedure 2 calls Query 2

You 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).

B
Post #1385064
Posted Thursday, November 15, 2012 3:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 461, Visits: 693
just 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 APPLY
Post #1385066
Posted Thursday, November 15, 2012 4:19 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:48 AM
Points: 76, Visits: 346
i have the following:
Stored Procedure 1 calls Query 1
Stored Procedure 2 calls Query 2

i would like to have:
Stored Procedure 3 calling Query 1 and Query 2 and then combine results.

Query 1 is to select data from table1
Query 2 is to select data from table2

Both table1 and table2 have same structure..

My actual need is
combine data from Query 1 and data from Query2 ,and return single data


Post #1385079
Posted Thursday, November 15, 2012 6:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 12,953, Visits: 32,480
sumith1andonly1 (11/15/2012)
i have the following:
Stored Procedure 1 calls Query 1
Stored Procedure 2 calls Query 2

i would like to have:
Stored Procedure 3 calling Query 1 and Query 2 and then combine results.

Query 1 is to select data from table1
Query 2 is to select data from table2

Both table1 and table2 have same structure..

My actual need is
combine data from Query 1 and data from Query2 ,and return single data



details..we need details.
while it's technically possible 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.

SELECT  *
FROM OPENROWSET( 'SQLNCLI',
'Server=(local);Trusted_Connection=yes;',
'SET FMTONLY OFF; SET NOCOUNT ON; exec Db_Name.dbo.spGetCategoriesByDocIDAsTable 811'
)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1385109
Posted Thursday, November 15, 2012 6:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 12,953, Visits: 32,480
another , 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.
    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 < 50
END



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1385112
Posted Friday, November 16, 2012 12:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Lowell beat me to it...use temp tables.

Stored Procedure 3 calls Stored Procedure 1 which puts the results1 into #TempTable
Stored Procedure 3 calls Stored Procedure 2 which puts the results2 into #TempTable

Since 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:


IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[Col1] INT NULL,
[Col2] NVARCHAR(50) NULL,
PRIMARY KEY (ID),
UNIQUE (ID))

INSERT INTO #TempTable
EXEC dbo.myProc1 -- the rows being inserted must match those of #TempTable

INSERT INTO #TempTable
EXEC dbo.myProc2

SELECT
ID
,Col1
,Col2
FROM
#TempTable


Post #1385818
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse