SEO

I hope I am not asking about something that has been done before, but I
have searched and cannot find an answer. What I am trying to do is to
run a query, and then perform some logic on the rowcount and then
possibly display the result of the query. I know it can be done with
ADO, but I need to do it in Query Analyzer. The query looks like this:

select Var
from DB
where SomeCriteria

if @@Rowcount = 0
select 'n/a'
else if @@Rowcount = 1
select -- this is the part where I need to redisplay the result
from the above query
else if @@Rowcount > 1
-- do something else

The reason that I want to do it without re-running the query is that I
want to minimize impact on the DB, and the reason that I can't use
another program is that I do not have a develpment environment where I
need to run the queries. I would select the data into a temp table, but
again, I am concerned about impacting the DB. Any suggestions would be
greatly appreciated. I am really hoping there is something as simple as
@@resultset, or something to that effect.

(johntarr@gmail.com) writes:
> I hope I am not asking about something that has been done before, but I
> have searched and cannot find an answer. What I am trying to do is to
> run a query, and then perform some logic on the rowcount and then
> possibly display the result of the query. I know it can be done with
> ADO, but I need to do it in Query Analyzer. The query looks like this:
>
> select Var
> from DB
> where SomeCriteria
>
> if @@Rowcount = 0
> select 'n/a'
> else if @@Rowcount = 1
> select -- this is the part where I need to redisplay the result
> from the above query
> else if @@Rowcount > 1
> -- do something else

I hope you know that @@rowcount is volatile, so the above logic would
have be replaced with:

SELECT @rowc = @@rowcont
IF @rowc = 0
...

> The reason that I want to do it without re-running the query is that I
> want to minimize impact on the DB, and the reason that I can't use
> another program is that I do not have a develpment environment where I
> need to run the queries. I would select the data into a temp table, but
> again, I am concerned about impacting the DB. Any suggestions would be
> greatly appreciated. I am really hoping there is something as simple as
> @@resultset, or something to that effect.

There is no such thing in SQL Server, but judging from the logic
above, you only want one row, and in this case you could bounce the
data over variables:

SELECT @col1 = col1, @col2 = col2, ...
...
IF @rowc = 1
SELECT col1 = @col1, col2 = @col2, ...

If the result set have multiple rows, you will have to re-run the query
or use a temp table. Or just skip the n/a thing.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I appreciate the advice on @@rowset and I will be sure to set it to a
variable. The problem is that quite often there are multiple rows
returned, so I am forced to either re-run the query (which I don't want
to do because it may bog down the DB). Or I could select into a temp
table, but again I am concerned about a performance hit.

just.an.imbecile (johntarr@gmail.com) writes:
> I appreciate the advice on @@rowset and I will be sure to set it to a
> variable. The problem is that quite often there are multiple rows
> returned, so I am forced to either re-run the query (which I don't want
> to do because it may bog down the DB). Or I could select into a temp
> table, but again I am concerned about a performance hit.

If your main concern is to keep down the load, then just skip extras
with the 'n/a' stuff, and just run the SELECT right away.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I actually got it working. I was using QuickKeys to automate a process
and found a way around re-running the query. However, for future
reference, are you saying that if you run a query and then re-run the
same query shortly therafter then the second time it doesn't have as
much of an impact on the DB?

just.an.imbecile (johntarr@gmail.com) writes:
> I actually got it working. I was using QuickKeys to automate a process
> and found a way around re-running the query. However, for future
> reference, are you saying that if you run a query and then re-run the
> same query shortly therafter then the second time it doesn't have as
> much of an impact on the DB?

I didn't really say that. But the impact on the system may be somewhat
smaller the second time, because all data would likely to be in the cache.

The impact of bouncing the data over a temp table, rather than running
the query twice is likely to have less impact.

Then again, it depends very much on the query. A query than retrieves
a couple of rows using indexes and runs sub-second, is nothing to bother
about. A query that has to scan a 10-million row table is nothing to take
lightly in the production environment.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

first time through select into a temp table.

then you can play with the temp table without having to go back to main
query.