Prev: LEFT JOIN and unnecessary reads
Next: Audit trigger
From: simon on 8 Apr 2010 04:58
I have common table expression and inside I have complicated SELECT
statement with many JOINS AND WHERE clauses. I don't want to execute
this SELECT INSIDE CTE twice, once for count and once for actual
SELECT, so I wonder if it is possible to write something similar like
this:

;WITH CTE(SELECT * FROM table)
IF (SELECT COUNT(*) FROM CTE1)>100
SELECT -1
ELSE
SELECT * FROM CTE

I know I can do like this, but I don't want to execute inner select
from table twice:
IF (SELECT COUNT(*) FROM table)>100
SELECT -1
ELSE
;WITH CTE(SELECT * FROM table)
SELECT * FROM CTE

Any idea? Use temp table instead of CTE is one, but I would like to
know if it's posible to do with CTE.

Thank you,
Simon
From: Uri Dimant on 8 Apr 2010 05:39
Simon
I do not think that CTE is a good candidate for what you are trying to
achive, why not using a temporary table?

"simon" <zupan.net(a)gmail.com> wrote in message
news:4e717704-cb85-4992-8a7b-3d15140e7375(a)h27g2000yqm.googlegroups.com...
>I have common table expression and inside I have complicated SELECT
> statement with many JOINS AND WHERE clauses. I don't want to execute
> this SELECT INSIDE CTE twice, once for count and once for actual
> SELECT, so I wonder if it is possible to write something similar like
> this:
>
> ;WITH CTE(SELECT * FROM table)
> IF (SELECT COUNT(*) FROM CTE1)>100
> SELECT -1
> ELSE
> SELECT * FROM CTE
>
> I know I can do like this, but I don't want to execute inner select
> from table twice:
> IF (SELECT COUNT(*) FROM table)>100
> SELECT -1
> ELSE
> ;WITH CTE(SELECT * FROM table)
> SELECT * FROM CTE
>
> Any idea? Use temp table instead of CTE is one, but I would like to
> know if it's posible to do with CTE.
>
> Thank you,
> Simon


From: Stefan Hoffmann on 8 Apr 2010 05:39
hi Simon,

On 08.04.2010 10:58, simon wrote:
> ;WITH CTE(SELECT * FROM table)
> IF (SELECT COUNT(*) FROM CTE1)>100
> SELECT -1
> ELSE
> SELECT * FROM CTE
Does this make sense?

In the frist case you retun a single int and in the other a field
list... where and how do you like to process this wierd kind of result?

btw, what is CTE1?



mfG
--> stefan <--
From: simon on 8 Apr 2010 07:42
On 8 apr., 11:39, Stefan Hoffmann <ste...(a)ste5an.de> wrote:
> hi Simon,
>
> On 08.04.2010 10:58, simon wrote:> ;WITH CTE(SELECT * FROM table)
> > IF (SELECT COUNT(*) FROM CTE1)>100
> >      SELECT -1
> > ELSE
> >      SELECT * FROM CTE
>
> Does this make sense?
>
> In the frist case you retun a single int and in the other a field
> list... where and how do you like to process this wierd kind of result?
>
> btw, what is CTE1?
>
> mfG
> --> stefan <--

Hi Stefan,

this is just an quick example, not real one. CTE1 in mistake, it
should be CTE.
The point is if result set is too large, I return a message to user,
that it should use filter to shorten the result set.
Otherwise it would kill server performance if I use ORDER BY on
milion records (and user can order each column of result set).

So I created temp table and put inside only 100 or 1000 or 5000
records(it depends on user selection) and than i check count and order
only those and return only results for one page in grid.
Temp table is the only way(like Uri wrote - thank you Uri). I was
wondering if there is option with CTE also.
I think that CTE should have also possibility to use their results in
some IF statements or similar, maybe in some next version of SQL
server.

have a nice day,
Simon


From: Plamen Ratchev on 8 Apr 2010 10:06
Try something like this:

;WITH CTE AS (SELECT <columns>, COUNT(*) OVER() AS cnt FROM table)
SELECT <columns> FROM CTE WHERE cnt <= 100;

IF @@ROWCOUNT = 0 RETURN -1;


--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2 3
Prev: LEFT JOIN and unnecessary reads
Next: Audit trigger