Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


combining result in stored procedure ??


combining result in stored procedure ??

Author
Message
sumith1andonly1
sumith1andonly1
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
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??
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
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.
bleroy
bleroy
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 739
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
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
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
sumith1andonly1
sumith1andonly1
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1721
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search