INvariant part inside SELECT

SQL Apprentice Question
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?


Celko Answers

>> Just schematically: the SP should return the number of records [sic] if the parameter @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.


>> The idea described below doesn't work... <<


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?


>> --Parameter


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.



>> 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. <<


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?