如何检查0 ROWCOUNT

Question

我正在运行一个SQL查询，该查询可能返回一个空行集。我想用CASE语句来处理这件事。

这是我试过但不起作用的东西

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

case语句不返回任何值。

如果我使用print @@RowCount而不是Select得到一个0值。

我怎么才能解决这个问题？

好的，这个查询相当大，所以我将尝试解释它的功能。我使用这个函数从我们的人力资源数据库返回数据。我需要员工的信息，如姓名和工作分配。在有些情况下，我需要处理员工未知的数据。在这些情况下，我传递一个employeeId数为零。这将导致在我运行查询时返回空值。

我想要做的是让函数将名称数据作为未知返回。

希望这有助于稍微澄清一下请求。

我已经修改了查询，以便显示以下所有相关部分：

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

我的一位同事解决了这个问题，方法是在开始时检查员工ID的值，然后用下面的代码返回未知的值。

> 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

问题在于如何设置变量。当您使用SET时，如果不返回任何行，它将将该值更改为NULL。

相反，您应该修改代码，这样它就可以执行类似的操作。

SET @ReturnValue = 'Unknown'

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

这会将初始值设置为未知。这将在您的所有IF语句之前，并在整个集合中工作。如果没有返回行，则值将保持不变。我删除了前1，因为它是不必要的。如果您有多个行，它将从结果集中的最后一行接收值。如果有很多行，您可以始终使用MAX或MIN。

Answer 3

@Perry，以下是您所要寻找的内容：

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

我还没有测试代码，因为我不在我的桌面上，所以请原谅任何小的语法错误，但是它应该可以实现您所需要的。