SQL @@RowCount on Select statement

问题:

I have a select statement. I would like to know if this select statement returns any rows or not. Based on the record count I would like to perform some other action.

I tried using @@RowCount to get the count but it does not seem to work properly with select statements.

Any suggestions on how this should be used or any other way I could perform my post task after select.

select    
    isnull(TotalHoursWorkedAmount, 0) / 26  
from    
    EmployeeStatus ESI
where   
    ESI.EmployeeCode = @employee_id

Adding the other piece

if @@ROWCOUNT=0
    SELECT dbo.fxGetAverage(@employee_id)  

回答1:

@@ROWCOUNT does work with select statements. You did not show your attempt, so I can't really say what you're doing wrong.

but anyway, this would provide the rowcount as an example:

select    isnull(TotalHoursWorkedAmount, 0) / 26  
    from    EmployeeStatus ESI
    where   ESI.EmployeeCode = @employee_id

select @@rowcount

or store it in a variable:

declare @myCount int

select    isnull(TotalHoursWorkedAmount, 0) / 26  
    from    EmployeeStatus ESI
    where   ESI.EmployeeCode = @employee_id

set @myCount = @@rowcount

And as others stated, if it's just a true/false question - as opposed to do x if y number of rows, z if v number of rows - then you're better off doing an exists, unless you actually need the results of the select.

if exists (    
    select  1
    from    EmployeeStatus ESI
    where   ESI.EmployeeCode = @employee_id
)
    begin
      print 'do stuff'
    end

回答2:

IF for some reason you cant get @@rowcount working you can use this query to check if you are getting any results. using if statement.

if not exists(select isnull(TotalHoursWorkedAmount, 0) / 26  from EmployeeStatus ESI where ESI.EmployeeCode = @employee_id)
     begin
          SELECT dbo.fxGetAverage(@employee_id) 
     end