How Can I check for a 0 ROWCOUNT

Question

I am running a SQL query that may return an empty rowset. I want to handle that with using a CASE statement.

This what I have tried but is not working

Case
When @@ROWCOUNT > 0 Then LTRIM(RTRIM(t.FirstName))  Else 'UNKNOWN'
End

The case statement does not return any value

If I use print @@RowCount rather then Select I get a 0 value.

How can I solve this?

Okay the query is rather large so I'll try to explain what it does. I am using this in a function that returns data from our HR database. I need Employee information such as name and work assignments. There are cases where I need to process the data where the employee is unknown. In those cases I pass an employeeId number of zero. That results in an empty return when I run the query.

What I would like to do is have the function return the Name data as UNKNOWN.

Hope this helps clear up the request a little

I have trimmed up the query so I can show all of the relevant parts below:

DECLARE @EID int = 0
DECLARE @AssignmentType varchar(max) = 'FirstName'
DECLARE @ReturnValue varchar(max)
DECLARE @EIDTemp TABLE(EID varchar(6), Section varchar(max), Division 
varchar(max), Bureau varchar(max), FirstName varchar(max), MiddleName 
varchar(max), LastName varchar(max));

Insert Into @EIDTemp
SELECT L1.Employee, P1.NAME As Section,
CASE 
    WHEN L1.DEPARTMENT IN (7010) THEN 'Legal Services Division' 
    WHEN L1.DEPARTMENT IN (7030,7040) THEN 'Fiscal Management Division'
    Else 'Other'
END AS Division,
CASE
    WHEN L1.DEPARTMENT IN (7130) THEN 'Administrative Services Bureau'
    ELSE ','
END As Bureau
,L1.FIRST_NAME
,L1.MIDDLE_NAME
,L1.LAST_NAME
FROM [SOOPS-LAWREPT].[LAWDATA].dbo.EMPLOYEE L1
 JOIN [SOOPS-LAWREPT].[LAWDATA].dbo.DEPTCODE L2 ON L1.Department = 
L2.Department
 JOIN [SOOPS-LAWREPT].[LAWDATA].dbo.JOBCODE L3 on L1.JOB_CODE = L3.JOB_CODE
 JOIN [SOOPS-LAWREPT].[LAWDATA].dbo.PRSYSTEM P1 ON L1.PROCESS_LEVEL = 
p1.PROCESS_LEVEL
WHERE  L1.EMP_STATUS='A1'


-- Select the Return Value
-------------------------------


IF (@AssignmentType = 'FirstName')
BEGIN
SET @ReturnValue = (SELECT TOP 1  LTRIM(RTRIM(t.MiddleName)) From @EIDTemp t 
where EID = @EID);
END

IF (@AssignmentType = 'MiddleName')
BEGIN
SET @ReturnValue = (SELECT TOP 1  LTRIM(RTRIM(t.MiddleName)) From @EIDTemp t 
where EID = @EID);
END

IF (@AssignmentType = 'LastName')
BEGIN
SET @ReturnValue = (SELECT TOP 1  LTRIM(RTRIM(t.LastName)) From @EIDTemp t 
where EID = @EID);
END

Answer 1

A co-worker of mine solved the problem by checking the value of the Employee ID at the start and returning UNKNOWN with this code

> DECLARE @EID int = 0
DECLARE @AssignmentType varchar(max) = 'FirstName'

DECLARE @ReturnValue varchar(max) = 'UNKNOWN'
DECLARE @EIDTemp TABLE(EID varchar(6), Section varchar(max), Division 
varchar(max), Bureau varchar(max), FirstName varchar(max), MiddleName 
varchar(max), LastName varchar(max));

if @EID = 0
   goto done

-- Insert into a Temp Table
-------------------------------
Insert Into @EIDTemp
SELECT L1.Employee, P1.NAME As Section,
   CASE 
          WHEN L1.DEPARTMENT IN (7010) THEN 'Legal Services Division' 
          WHEN L1.DEPARTMENT IN (7030,7040) THEN 'Fiscal Management 
Division'
          Else 'Other'
   END AS Division,
   CASE
          WHEN L1.DEPARTMENT IN (7130) THEN 'Administrative Services Bureau'
          ELSE ','
   END As Bureau
   ,L1.FIRST_NAME
   ,L1.MIDDLE_NAME
   ,L1.LAST_NAME
FROM [SOOPS-LAWREPT].[LAWDATA].dbo.EMPLOYEE L1
   JOIN [SOOPS-LAWREPT].[LAWDATA].dbo.DEPTCODE L2 ON L1.Department = 
L2.Department
   JOIN [SOOPS-LAWREPT].[LAWDATA].dbo.JOBCODE L3 on L1.JOB_CODE = 
L3.JOB_CODE
   JOIN [SOOPS-LAWREPT].[LAWDATA].dbo.PRSYSTEM P1 ON L1.PROCESS_LEVEL = 
p1.PROCESS_LEVEL
WHERE  L1.EMP_STATUS='A1' AND l1.EMPLOYEE = @EID


-- Select the Return Value
-------------------------------


IF (@AssignmentType = 'FirstName')
BEGIN
   SET @ReturnValue = (SELECT TOP 1  LTRIM(RTRIM(t.FirstName)) From @EIDTemp 
t where EID = @EID);
END

IF (@AssignmentType = 'MiddleName')
BEGIN
   SET @ReturnValue = (SELECT TOP 1  LTRIM(RTRIM(t.MiddleName)) From 
@EIDTemp t where EID = @EID);
END

IF (@AssignmentType = 'LastName')
BEGIN
   SET @ReturnValue = (SELECT TOP 1  LTRIM(RTRIM(t.LastName)) From @EIDTemp 
t where EID = @EID);
END

done:

print @ReturnValue

Answer 2

You need to save the @@RowCount variable immediately after running the insert/select and return the variable. If you don't save the variable, another statement will eventually clear the rowcount...

Example:

--Variable to hold the rowcount
DECLARE @Count int = 0
...

Insert Into @EIDTemp
SELECT L1.Employee, P1.NAME As Section,
...
WHERE  L1.EMP_STATUS='A1'

--Save the result
SELECT @Count=@@RowCount

...
--Use the saved result
Case
    When @Count > 0 Then LTRIM(RTRIM(t.FirstName))  Else 'UNKNOWN'
End

Answer 3

@Perry, is the following more like what you are looking for:

WITH employee_ids AS
(
    SELECT 
        emp.Employee AS eid
        ,P1.NAME As Section
        ,CASE 
            WHEN emp.DEPARTMENT IN (7010) THEN 
                'Legal Services Division' 
            WHEN emp.DEPARTMENT IN (7030,7040) THEN 
                'Fiscal Management Division'
            ELSE
                'Other'
            END AS Division
        ,CASE
            WHEN emp.DEPARTMENT IN (7130) THEN 
                'Administrative Services Bureau'
            ELSE 
                ','
            END As Bureau
        ,emp.FIRST_NAME
        ,emp.MIDDLE_NAME
        ,emp.LAST_NAME
        ,ROW_NUMBER() OVER (PARTITION BY emp.EMPLOYEE) AS eid_match

    FROM
        [SOOPS-LAWREPT].[LAWDATA].dbo.EMPLOYEE AS emp

    INNER JOIN 
        [SOOPS-LAWREPT].[LAWDATA].dbo.DEPTCODE AS depts 
        ON (emp.Department = depts.Department)

    INNER JOIN 
        [SOOPS-LAWREPT].[LAWDATA].dbo.JOBCODE AS jcs 
        ON (emp.JOB_CODE = jcs.JOB_CODE)

    INNER JOIN 
        [SOOPS-LAWREPT].[LAWDATA].dbo.PRSYSTEM AS prs 
        ON (emp.PROCESS_LEVEL = prs.PROCESS_LEVEL)

    WHERE
        emp.EMP_STATUS='A1'
)
,provisional_results AS
(
    SELECT
        CASE @AssignmentType
            WHEN 'FirstName' THEN
                employee_ids.FirstName
            WHEN 'MiddleName' THEN
                employee_ids.MiddleName
            WHEN 'LastName' THEN
                employee_ids.LastName
            ELSE
                NULL
            END AS provisional_return_value

    FROM 
        (VALUES (0)) AS default_values(default_value)

    LEFT JOIN 
        employee_ids
        ON (employee_ids.eid = @eid) 
        AND (eid_match = 1)
)
SELECT 
    @ReturnValue = ISNULL(LTRIM(RTRIM(provisional_return_value)), 'UNKNOWN') 
FROM 
    provisional_results

I haven't tested the code as I'm not at my desktop so excuse any small syntax errors, but it should achieve what you need.

Answer 4

The problem is how you are setting your variables. When you use SET it will change the value to NULL if no rows are returned.

You should instead change up your code so it does something like this.

SET @ReturnValue = 'Unknown'

IF (@AssignmentType = 'FirstName')
BEGIN
SELECT @ReturnValue = LTRIM(RTRIM(t.MiddleName)) From @EIDTemp t 
where EID = @EID
END

This sets an initial value to Unknown. This would be before all of your IF statements and would work across the whole collection. If there are no rows returned the value will remain unchanged. I removed the TOP 1 because it is not needed. If you have multiple rows it will receive the value from the last row in the result set. If there are lots of rows you could always use MAX or MIN.