如何检查重复是SQL存储过程 [英] How to check duplication is SQL Stored procedure

问题描述

我具有以下值，我需要检查重复项.

empno deptid
----- ------
101 1
102 2
103 3

检查重复条件
-----------------------------

1)如果empno为null并且depid =``1''
错误部门已存在"
2)如果empno =``101''和depid =''''
错误"Emp已经存在"
3)如果empno =``101''并且depid =``1''
错误"Emp/Dept已经存在"
ELSE
将值插入表

I have the values as below and i need to check the duplication.

empno deptid
----- ------
101 1
102 2
103 3

checking duplicates condition
-----------------------------

1) if empno is null and depid = ''1''
ERROR "Dept Already Exists"
2) if empno = ''101'' and depid = ''''
ERROR "Emp Already Exists"
3) if empno = ''101'' and depid = ''1''
ERROR "Emp/Dept already exist"
ELSE
INSERT the values into table

推荐答案

IF (SELECT COUNT(*) FROM tb WHERE empno=@empno AND deptid=@deptid ) > 0
 PRINT "Emp/Dept already exist&";
ELSE IF (SELECT COUNT(*) FROM tb WHERE empno IS NULL AND deptid=@deptid ) > 0
 PRINT "ERROR Dept already exists";
ELSE IF  (SELECT COUNT(*) FROM tb WHERE empno=@empno AND deptid='') > 0
 PRINT"Emp Already Exists&";
ELSE
 INSERT INTO tb VALUES(@empno, @deptid)

tb-您的表@ empno，@ deptid-存储过程输入参数

tb - your table, @empno,@deptid - stored procedure input parameters

这篇关于如何检查重复是SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！