INvariant part inside SELECT

All,
Can we do this trick and if yes then how? Just schematically: the SP should
return the number of records if the parameter @.Count=1, if not, then the
records themselves. The problem is that there is some complicated JOIN and
the whole set of WHERE clauses that I wouldn't like to repeat in two
different queries looking almost identically excluding the main SELECT part.
The idea described below doesn't work.
--Parameter
Declare @.Count bit
SET @.Count = 1
SELECT
CASE
WHEN @.Count = 1
THEN pe.*, pn.*
ELSE COUNT(*)
END
...
FROM ...
INNER JOIN ... ON ...
WHERE ...
Any ideas?
Just D.Just D (no@.spam.please) writes:
> Can we do this trick and if yes then how? Just schematically: the SP
> should return the number of records if the parameter @.Count=1, if not,
> then the records themselves. The problem is that there is some
> complicated JOIN and the whole set of WHERE clauses that I wouldn't like
> to repeat in two different queries looking almost identically excluding
> the main SELECT part. The idea described below doesn't work.
The best is probably to put the whole JOIN-WHERE business in an
inline table-valued function. Then the procedure can read:
IF @.count = 1
SELECT COUNT(*) FROM tblfunc(@.par1, @.par2, ...)
ELSE
SELECT col1, col2, ...
FROM tblfunc (@.par1, @.par2, ...)
You could also bounce the data over a temp tble, but that would be more
expensive in terms of performance, not the least for the COUNT. (Since for
the COUNT(*) SQL Server may find a quicker query plan when it does not have
to read all data pages.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
Using SELECT * in production code is not a good idea. The only way to do
what you are doing without writing the query twice would be to use dynamic
SQL.
John
"Just D" wrote:

> All,
> Can we do this trick and if yes then how? Just schematically: the SP shoul
d
> return the number of records if the parameter @.Count=1, if not, then the
> records themselves. The problem is that there is some complicated JOIN and
> the whole set of WHERE clauses that I wouldn't like to repeat in two
> different queries looking almost identically excluding the main SELECT par
t.
> The idea described below doesn't work.
> --Parameter
> Declare @.Count bit
> SET @.Count = 1
>
> SELECT
> CASE
> WHEN @.Count = 1
> THEN pe.*, pn.*
> ELSE COUNT(*)
> END
> ...
> FROM ...
> INNER JOIN ... ON ...
> WHERE ...
> Any ideas?
> Just D.
>
>|||Erland,
Correct me if I am wrong. I don't see any performance benifit by using the
table valued function over using the actual query, except for the fact that
the stored procedure looks better :)
The execution plan is not stored for the TVF but is stored in the calling
SP. And the plan will be recomplied everytime the condition changes. I would
say it would be better performance wise, if we have two stored procedures on
e
for returning the row count and one for returning the result set and call
these two SPs from the main SP based on the condition.So that only the main
SP will get recompiled and will not be much of an overhead.
If its a query with a simple execution plan, then what you suggest will be
fine.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Erland Sommarskog" wrote:

> Just D (no@.spam.please) writes:
> The best is probably to put the whole JOIN-WHERE business in an
> inline table-valued function. Then the procedure can read:
> IF @.count = 1
> SELECT COUNT(*) FROM tblfunc(@.par1, @.par2, ...)
> ELSE
> SELECT col1, col2, ...
> FROM tblfunc (@.par1, @.par2, ...)
> You could also bounce the data over a temp tble, but that would be more
> expensive in terms of performance, not the least for the COUNT. (Since for
> the COUNT(*) SQL Server may find a quicker query plan when it does not hav
e
> to read all data pages.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||>> Just schematically: the SP should return the number of records [sic] if the parame
ter @.Count=1, if not, then the records [sic] themselves. <<
Did you ever have a software engineering course? Remember cohesion?
The idea that a properly designed code module will perform one
well-defined task. Good programmers do not write things that return
the square root of a number or translate Flemish depending on a
parameter.
Why did you make your low-level BIT flag a reserved word? Why are you
thinking in terms of assembly language style flags and variant records
instead of rows?
DECLARE @.Count bit
SET @.Count = 1
SELECT
CASE
WHEN @.Count = 1
THEN pe.*, pn.*
ELSE COUNT(*)
END
..
FROM ...
INNER JOIN ... ON ...
WHERE ... ; <<
CASE is an expression and not a control flow device. You can use an
IF-THEN-ELSE construct in T-SQL to mimic procedural coding with variant
records instead of using declarative coding.
Did you also notice that you want to return one column and then want to
return two columns? Arow in a relational table always has a fixed
number of columns, unlike records in a file. Basically, you are still
writing COBOL or some other procedural file-oriented language, but you
are doing it in SQL.
This is a simple matter of cut & paste, not the end of the world.
However, if you are just looking for a newsgroup kludge instead of a
real answer in one query, try:
SELECT
CASE WHEN @.assembly_language_flag = 1
THEN 'violated cohesion'
ELSE COUNT(*) END AS foobar,
CASE WHEN @.assembly_language_flag = 1
THEN PA.x
ELSE 'violated cohesion' END AS x,
etc.
FROM ..
Boy that is awful, isn't it?|||You're so kind as usual writing that in this style. :) Let me guess, you're
from the Western Ukraine, aren't you?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message

> Did you ever have a software engineering course? Remember cohesion?
> The idea that a properly designed code module will perform one
> well-defined task. Good programmers do not write things that return
Tell that to the MS coders (mostly contractors from India:)) who were
usually adding 20 and more parameters like NULL (reserved) to the method
parameter list.overriding one method tons of times. That was always MS
style.

> the square root of a number or translate Flemish depending on a
> parameter.
Yea-yea, pretty close.
The flame is closed.|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> Correct me if I am wrong. I don't see any performance benifit by using
> the table valued function over using the actual query, except for the
> fact that the stored procedure looks better :)
Correct, but the presumption was that Just D wanted to the procedure
to look better. That is, he did not want repeat the conditions. And I can
think of four ways to achieve this aim:
1) view/inlined table function.
2) bounce over temp table.
3) dynamic SQL.
4) pre-processor.
In my post I only discussed the first two options, and of these the
TVF gives better performance than the temp table.
In my opinion, using dynamic SQL introduces another level of complexity
which is not worth the pain in this case.
And preprocessor? Well, we have one in our environment, but most
people doesn't.

> The execution plan is not stored for the TVF but is stored in the calling
> SP. And the plan will be recomplied everytime the condition changes.
As I understood it, the JOIN and WHERE conditions of the query are
stable. As for the condition on whether to return COUNT or result set,
that should lead to any recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, thanks for your answer.
Yes, the main idea was to make the SP more flexible and maintainable. The
conditions are complex enough to repeat them more than one time, and that's
especially bad if we need to improve/modify them in future, we can easily
make a simple mistake doing that in two places, OR we will have to
copy/paste each time we need to change something. That's why this idea
appeared. But from another side any change like that should not seriously
affect the speed of the code or the whole complexity because having this
divantage we're getting into another trouble which is not acceptable.
That's why I asked this newsgroup for a new, better idea. To implement the
function - then we'll need to maintain this function and provide the
required set of tables and parameters that should be cached in a different
way I guess if we call the function inside our SP. Temporary table - it's
even the worst scenario. Many different ways are able to change the whole
idea and to do one thing crashing all around.
Just D.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E0F409CDF8DYazorman@.127.0.0.1...
> Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> Correct, but the presumption was that Just D wanted to the procedure
> to look better. That is, he did not want repeat the conditions. And I can
> think of four ways to achieve this aim:
> 1) view/inlined table function.
> 2) bounce over temp table.
> 3) dynamic SQL.
> 4) pre-processor.
> In my post I only discussed the first two options, and of these the
> TVF gives better performance than the temp table.
> In my opinion, using dynamic SQL introduces another level of complexity
> which is not worth the pain in this case.
> And preprocessor? Well, we have one in our environment, but most
> people doesn't.
>
> As I understood it, the JOIN and WHERE conditions of the query are
> stable. As for the condition on whether to return COUNT or result set,
> that should lead to any recompilation.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just D (no@.spam.please) writes:
> That's why I asked this newsgroup for a new, better idea. To implement the
> function - then we'll need to maintain this function and provide the
> required set of tables and parameters that should be cached in a different
> way I guess if we call the function inside our SP.
Not really sure what you mean here. An inline-table function does not have
any query plan of its own. An inline table function is really a macro that
the optimizer pastes in before building the query plan. (Note that this
does not apply to multi-statement functions nor to scalar functions.)
As for the maintenance, you would move that to the function. The procedure
would just be a wrapper on the function.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx