Union 2 Stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Union 2 Stored Procedures

Author

Topic

chad483
Starting Member

5 Posts

Posted - 2011-03-21 : 17:47:08

Hi Everyone,

I am using Access 2000 as a front end to access my SQL database. I have 2 stored procedures, and I need to do a union on them. Everthing I have read says I need to put them each into a table and then do a full union on the 2 tables, but I cannot get the data into the tables. Am I attacking this the right way? I am new to all of this, so baby steps would be great.
Thanks
Chad

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 17:52:37

There might be an easier way to do this, but this should work:

1. Create a pass-through query for each stored procedure, with "EXEC myProcedureName" as the SQL (change myProcedureName to the actual procedure)
2. Create an Access Union query with those 2 pass-through queries as the source.

If that fails, then this should work:

1. Repeat step 1 as described earlier.
2. Create a Make Table query for each of the pass-through queries. Make sure to use 2 different table names.
3. Run the Make Table query for each.
4. Create a Union query that uses those 2 tables as the source.

chad483
Starting Member

5 Posts

Posted - 2011-03-21 : 17:59:48

Thanks for the quick reply. One question, you mentioned Queries throughout your post. I don't have the option of queries. the DB that has been created is a *.adp and I can only use stored procedures. Will this still work?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 18:10:18

Not sure, I've never used ADPs in Access.

You can't normally union 2 stored procedures in SQL Server, but you could write a 3rd procedure that performs the same logic as both procedures, then outputs a union of their results. You could also create a table with the same structure as the output of the procedures, and do something like this:

TRUNCATE TABLE resultsTable
INSERT resultsTable EXEC myProcedure1
INSERT resultsTable EXEC myProcedure2
SELECT * FROM resultsTable -- this is actually a UNION ALL
SELECT DISTINCT * FROM resultsTable -- this is a proper UNION

chad483
Starting Member

5 Posts

Posted - 2011-03-21 : 18:16:35

As I mentioned, I am very new at this. I found this code and modified it to my needs
CREATE TABLE #OC_tmp(
Date nchar(10),
WorkLocation nchar(10),
Bench smallint,
MaterialType nchar(10),
OC_Tonnes int
)
INSERT INTO #OC_tmp execute Merge_OCData
SELECT * FROM #OC_tmp

But nothing happens. I know it is a temp table, and will get dropped later, but I was expecting to see my data popup in a table, but it appears to do nothing. Do yo see anything wrong with this?

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources