November 15, 2012 at 3:42 am
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??
November 15, 2012 at 3:54 am
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.
November 15, 2012 at 3:54 am
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
November 15, 2012 at 3:55 am
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
November 15, 2012 at 4:19 am
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
November 15, 2012 at 6:04 am
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
November 15, 2012 at 6:10 am
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
November 16, 2012 at 12:01 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply