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.
| Author |
Topic |
|
Munchausen
Starting Member
25 Posts |
 Posted - 2005-07-07 : 14:53:27
|
| Hi there!I'm trying to migrate several queries from Access to SQL Server stored procedures, and am having some problems.There are several queries, and they tend to reference each other. For instance, QueryOne may state something like "SELECT QueryTwo.ColumnFromQueryTwo...", which doesn't seem to work in SQL Server.Is there someway to achieve this same functionality? I'd like to keep the queries in separate procedures, if possible.Thanks in advance for any help. |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-07-07 : 14:56:17
|
| Is it needing just one return value or does it return multiple columns? If it is one value, then a stored proc can be called within a stored proc by using the EXEC statement. If it needs to reference multiple columns in the stored proc you might consider using a view or a user defined function.Aj |
 |
|
|
Munchausen
Starting Member
25 Posts |
Posted - 2005-07-07 : 15:00:27
|
| All of the queries return multiple columns, and they SELECT several columns each from other queries (except for the "bottom" ones, they're hierarchal in how they relate).Hope that makes sense. |
|
|
|
Munchausen
Starting Member
25 Posts |
Posted - 2005-07-07 : 15:22:32
|
| I figured a view might be the way to go here. Thanks for the suggestion. I'm wondering if I can use a stored procedure to create it, however.For instance, I tried:CREATE VIEW PreQry ASEXEC ThePreQueryand I'm getting a bad syntax error. |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-07-08 : 09:45:58
|
| You could also use a function if you need to pass variables around. Views are usually a bit faster from what I have read.I would copy the code out of the stored proc and just use it within the view instead of executing a stored proc. Aj |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-07-08 : 09:55:23
|
I would take a look at what the series of queries are actually doing in access, and then rewrite in SQL to acquire the same results.TRUST ME!We had a series of queries in access (32 queries). It has been fully converted to SQL, and it is now 1 stored procedure. (granted it does use a few temp tables ~ about 4) but it is literally 600 times faster than the query within query approach that it went to when we first migrated to access. Spend the time now, and do it right  Corey Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."  |
|
|
|
|
|
|
|
|
Stored procedures within stored procedures?