Multiple queries in one Stored Procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple queries in one Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-07 : 07:58:29

writes "Is there a way, using SQL, that I can accomplish the following in 1 stored procedure? I'm not sure how to, or if I can, reference output from a query or stored procedure from within a stored procedure:
1) SELECT fields FROM TABLE1 ... QUERY1
2) JOIN TABLE2 to QUERY1 and GROUP ... QUERY2
** 1) and 2) can be combined using Subqueries
3) SELECT fields FROM TABLE2 and TABLE3 ... QUERY3
4) SELECT fields FROM QUERY2 UNION SELECT fields FROM QUERY3
I'm converting my ACCESS queries to SQL Server Views and Stored Procedures and have run into this problem as ACCESS allowed you to reference Queries as Tables. Thanks."

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-07 : 08:10:11

SELECT fields... -- Query2
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.XXX = T2.YYY
GROUP BY grouping-criteria

UNION ALL

SELECT fields.. -- Query3
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.AAA = T2.BBB

Sam

dsdeming

479 Posts

Posted - 2003-07-07 : 08:41:11

quote:
I'm converting my ACCESS queries to SQL Server Views and Stored Procedures and have run into this problem as ACCESS allowed you to reference Queries as Tables.

A SQL Server view is the equivalent of an Access query, and you can select from it like you would from a table. You can also select from derived tables:

SELECT a.name
FROM ( SELECT * FROM sysobjects WHERE xtype = 'V' ) a

Dennis

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 10:12:37

OOOOOOOHHHHH The infamous nested access queries...

I wish they would prevent that..

But then you couldn't get anything done in access (what a shame)

I would looke VERY CLOSELY at the access mess..

Do you see GROUP BY's with no aggregates?

Fields being SELECTED in the lower queries but are never referenced?

ORDER BY's on inner queries?

I've seen a bunch of bad designs...make sure you don't port them forward, and in the same breath, make sure they produce the same results (if they're correct that is).

And a view will work nicley

Sam, it's not a UNION thing. Each query is based on a query that's based on a query..ad infinitum.

The last one I saw was 13 levels deeps with several branches...

UUUUUUGLY

Did I mention I hate Access

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources