如何在SQL Server 2012的IF-ELSE控制语句中查看查询是否执行成功

Question

我将部门名称存储在变量@department中，现在我将从一行中获取该特定部门名称，我想知道departments表中是否存在部门id意味着用户输入的部门名称是否是有效的部门。如何做到这一点?如何将此查询转换为布尔表达式?

create table dbo.departments
(
    department_id int primary key;
    department_name varchar(255);
)
if(select @finaldepartment_id = department_id 
   from dbo.departments 
   where department_name like @department)
   set @department_flag = 1;
else
   set @department_flag = 0;

Answer 1

可以简化为单个查询

SET @department_flag = CASE 
                         WHEN EXISTS (SELECT 1 
                                      FROM   dbo.departments 
                                      WHERE  department_name LIKE Concat('%',@department,'%')) THEN 1
                         ELSE 0 
                       END 

Answer 2

检查是否有符合您条件的部门使用EXISTS

IF EXISTS (
  select 1 
  from dbo.departments 
  where department_name like '%'+@department+'%'
  )

如果你需要给一个变量分配一个部门的id，用结果设置变量，然后检查它是否为空。