@@rowcount without displaying select

7 Jul 2006 4:22 PM

peter walker

Hi, im hoping someone could shed some insight.

If i do the following in a trigger:

    select * from <table> where <condition>

    if @@rowcount > 0
    begin
        <perform tasks>
    end

.... but im not necessarily interested in displaying a rowset. The above
select statement will return a rowset for display wont it? Isnt there any
way to determine if @@rowcount > 0 without using a select? Im guessing a
select is harmful in some way as it returns a displayed row. But im not
interested in displaying the reulting row, I just want to know if there is
such a row. What impact dows the select statement have, given the fact im
not interested in the content of that particular row?

Any help most appreciated.

Cheers,
peter

7 Jul 2006 4:27 PM

Aaron Bertrand [SQL Server MVP]

IF EXISTS
(
SELECT 1 FROM <table> WHERE <condition>
)
BEGIN
PRINT 'There was at least one row.';
END

Show quoteHide quote

"peter walker" <p.walker@nospam.com> wrote in message
news:%23xei3EeoGHA.148@TK2MSFTNGP04.phx.gbl...
> Hi, im hoping someone could shed some insight.
>
> If i do the following in a trigger:
>
>    select * from <table> where <condition>
>
>    if @@rowcount > 0
>    begin
>        <perform tasks>
>    end
>
> ... but im not necessarily interested in displaying a rowset. The above
> select statement will return a rowset for display wont it? Isnt there any
> way to determine if @@rowcount > 0 without using a select? Im guessing a
> select is harmful in some way as it returns a displayed row. But im not
> interested in displaying the reulting row, I just want to know if there is
> such a row. What impact dows the select statement have, given the fact im
> not interested in the content of that particular row?
>
> Any help most appreciated.
>
> Cheers,
> peter
>

7 Jul 2006 4:27 PM

Barry

Peter,

You could use this instead...

     If (select Count(*) from <table> where <condition>) > 0

     begin
         <perform tasks>
     end

HTH

Barry

7 Jul 2006 4:29 PM

SQL Menace

Throw the count into a variable

declare @rowcount int
select @rowcount = count(*) from <table> where <condition>

if @rowcount > 0
.....
...
..

Denis the SQL Menace
http://sqlservercode.blogspot.com/

peter walker wrote:
Show quoteHide quote

> Hi, im hoping someone could shed some insight.
>
> If i do the following in a trigger:
>
>     select * from <table> where <condition>
>
>     if @@rowcount > 0
>     begin
>         <perform tasks>
>     end
>
> ... but im not necessarily interested in displaying a rowset. The above
> select statement will return a rowset for display wont it? Isnt there any
> way to determine if @@rowcount > 0 without using a select? Im guessing a
> select is harmful in some way as it returns a displayed row. But im not
> interested in displaying the reulting row, I just want to know if there is
> such a row. What impact dows the select statement have, given the fact im
> not interested in the content of that particular row?
>
> Any help most appreciated.
>
> Cheers,
> peter

7 Jul 2006 4:30 PM

SQL Menace

or better yet
if exists (select * from <table> where <condition>)
begin
<perform tasks>
end

Denis the SQL Menace
http://sqlservercode.blogspot.com/

peter walker wrote:
Show quoteHide quote

> Hi, im hoping someone could shed some insight.
>
> If i do the following in a trigger:
>
>     select * from <table> where <condition>
>
>     if @@rowcount > 0
>     begin
>         <perform tasks>
>     end
>
> ... but im not necessarily interested in displaying a rowset. The above
> select statement will return a rowset for display wont it? Isnt there any
> way to determine if @@rowcount > 0 without using a select? Im guessing a
> select is harmful in some way as it returns a displayed row. But im not
> interested in displaying the reulting row, I just want to know if there is
> such a row. What impact dows the select statement have, given the fact im
> not interested in the content of that particular row?
>
> Any help most appreciated.
>
> Cheers,
> peter

7 Jul 2006 4:31 PM

Vern Rabe

Try this:

IF EXISTS(SELECT 1 FROM <table> WHERE <condition>)
BEGIN
<perform tasks>
END

HTH
Vern Rabe

Show quoteHide quote

"peter walker" wrote:

> Hi, im hoping someone could shed some insight.
>
> If i do the following in a trigger:
>
>     select * from <table> where <condition>
>
>     if @@rowcount > 0
>     begin
>         <perform tasks>
>     end
>
> .... but im not necessarily interested in displaying a rowset. The above
> select statement will return a rowset for display wont it? Isnt there any
> way to determine if @@rowcount > 0 without using a select? Im guessing a
> select is harmful in some way as it returns a displayed row. But im not
> interested in displaying the reulting row, I just want to know if there is
> such a row. What impact dows the select statement have, given the fact im
> not interested in the content of that particular row?
>
> Any help most appreciated.
>
> Cheers,
> peter
>
>
>

7 Jul 2006 8:07 PM

Tracy McKibben

peter walker wrote:
Show quoteHide quote

> Hi, im hoping someone could shed some insight.
>
> If i do the following in a trigger:
>
>     select * from <table> where <condition>
>
>     if @@rowcount > 0
>     begin
>         <perform tasks>
>     end
>
> ... but im not necessarily interested in displaying a rowset. The above
> select statement will return a rowset for display wont it? Isnt there any
> way to determine if @@rowcount > 0 without using a select? Im guessing a
> select is harmful in some way as it returns a displayed row. But im not
> interested in displaying the reulting row, I just want to know if there is
> such a row. What impact dows the select statement have, given the fact im
> not interested in the content of that particular row?
>
> Any help most appreciated.
>
> Cheers,
> peter
>
>

As others have replied, you can use IF EXISTS or COUNT(*). Depending on
what you're doing in <perform tasks>, you might be able to avoid even
doing such a check. If <perform tasks> is merely another query, you may
be able to JOIN your check table, or use EXISTS in the where clause.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Thread options