November 14, 2011 at 11:22 am
I have a project that I would like to modularize. Can I call
Qry_1
from qry_2
from qry_3?
If I can call queries from other queries, can I use temp tables created from 1 query within another or do I have to create physical tables? Thanks
November 14, 2011 at 11:34 am
Yes you can, but that often has the cost of poorer performance.
What do you need to accomplish (business requirement side of things)?
November 14, 2011 at 12:11 pm
Yes, you can. It's not pretty, easy to maintain, or fun to look at. But, if used correctly, can be used to increase performance. In most cases, though, it isn't used correctly and it just slows things down.
Basically, you want to create a SELECT statement in the FROM clause. The trick is to not refer to any other tables in that sub-select statement. The statement must return a recordset (like a table does), then you join to it in the FROM clause.
Here is an example:
SELECTt1.col1, t2.col2
FROMtable1 t1
INNER JOIN (SELECTx.col1, x.col2, x.col3
FROMtable2 x
WHEREx.col1 = 'something') t2 ON t1.col1 = t2.col1
WHEREt1.col1 = 'somethingelse'
...
November 14, 2011 at 12:15 pm
I find it hard to think of a scenario where this would be the optimal choice.
I'd be interested to know the business requirements as Ninja requested.
Mike Scalise, PMP
https://www.michaelscalise.com
November 14, 2011 at 12:21 pm
mikes84 (11/14/2011)
I find it hard to think of a scenario where this would be the optimal choice.
Never said "optimal". It absolutely is horrid. However, when you are working with a system that you cannot create views this is the best way to accomplish some things, such as when you want to return a small subset of the data from a huge table. The alternative is to write subqueries in the select which is more of a performance hit than this solution.
I use to cringe whenever I saw this type of solution but have come to realize that there are times when it is the only way to do things.
...
November 14, 2011 at 12:37 pm
For example, I have several table drops to make and the top of my program is getting cluttered which is where I put all of my table drop code. I would like to put all of my drops in another query and call that query after I start my program.
Example:
IF OBJECT_ID('tempdb..#tbl.mmsfina') IS NOT NULL
drop table #tbl.mmsfina
ELSE
PRINT '#tbl.mmsfina does not exist.'
IF OBJECT_ID('tempdb..#tbl_tempmms') IS NOT NULL
drop table #tbl_tempmms
ELSE
PRINT '#tbl_tempmms does not exist.'
Also, I am writing my queries one section at a time as a separate query. Once I get my query written, instead of copying it into a master query, I would just include the name of the file holding the query.
This would make it easier for me to work on.
November 14, 2011 at 12:45 pm
cljolly (11/14/2011)
For example, I have several table drops to make and the top of my program is getting cluttered which is where I put all of my table drop code. I would like to put all of my drops in another query and call that query after I start my program.Example:
IF OBJECT_ID('tempdb..#tbl.mmsfina') IS NOT NULL
drop table #tbl.mmsfina
ELSE
PRINT '#tbl.mmsfina does not exist.'
IF OBJECT_ID('tempdb..#tbl_tempmms') IS NOT NULL
drop table #tbl_tempmms
ELSE
PRINT '#tbl_tempmms does not exist.'
Also, I am writing my queries one section at a time as a separate query. Once I get my query written, instead of copying it into a master query, I would just include the name of the file holding the query.
This would make it easier for me to work on.
Are you creating and dropping these tables in a stored procedure? If so, you don't need to test for their existence or drop them since local temporary tables are automatically dropped when the stored procedure exits. Would that help clear up some of the clutter?
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
November 14, 2011 at 12:59 pm
Some are temperary and some are permanent
November 14, 2011 at 1:48 pm
cljolly (11/14/2011)
Some are temperary and some are permanent
They should all be local temp. Why do you drop permament tables? How do you expect that code to function if 2 users use it at the same time?
I personally preffer to keep the drop at the top of the code. Runs in no time flat and make debugging easier next time around.
So if you're not trying to squeeze every last possible CPU cycle from the machine, then you're ok to leave them there.
November 14, 2011 at 1:49 pm
Just put all your code in 1 (n) sp(s) and call the correct sp, that makes only 1 line of code in your application and the clutter disappears.
Nothing wrong with what you seem to be doing at the moment.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply