Prev: LEFT JOIN and unnecessary reads
Next: Audit trigger
From: simon on 19 Apr 2010 08:11
On 16 apr., 18:35, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> If you execute your query with your limit (page size) + 1, you need to execute the CTE query only once, no need for temp
> table. On the next line after the query you can have the @@ROWCOUNT check and if limit + 1 rows are returned then you
> send message to the user, if less then you show the result set. You can return the @@ROWCOUNT to the client as output
> parameter and based on limit + 1 check take the appropriate action.
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Ok Plamen, that is what I'm looking for.
What is the syntax?

This simple example won't work:

;WITH CTE AS(SELECT 1 AS col1)
IF @@rowcount=2
SELECT -1
ELSE
SELECT * FROM CTE

Thank you,
Simon
From: Plamen Ratchev on 19 Apr 2010 21:46
I think you misunderstood my comment. There is no such syntax. I meant you can handle this in combination of client and
server side logic:

1). Run the CTE with TOP 101 (or limit + 1) rows
2). Send @@ROWCOUNT as output parameter
3). Client side check if @@ROWCOUNT is < (limit + 1). If yes, then show the resutl set. If no, then show message. This
way you end up sending a small result set to the client and always execute the CTE only once.

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