Posted by Erland Sommarskog on 10/02/39 11:44

(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]