做一个存储过程,在插入一条记录之前检查之前有没有已经重复的数据CREATE PROCEDURE CheckCPXY
...
select top 1 from CPXY where....return @ret就是想,如果select top 1查询出来有结果,则返回1,如果没有结果,则返回0,同时,可以在程序代码端读出来,查出来的结果的ID
...
select top 1 from CPXY where....return @ret就是想,如果select top 1查询出来有结果,则返回1,如果没有结果,则返回0,同时,可以在程序代码端读出来,查出来的结果的ID
go
CREATE PROCEDURE CheckCPXY
@ret int output
as
begin
if exists(select top 1 from CPXY where....)
set @ret=1
else
set @ret=0return @ret
end;declare @ret int
exec CheckCPXY @ret output
select @ret
--弄返回两个参数就好了,一为状态0或1,二为ID:
CREATE PROCEDURE P_CheckCPXY @status int OUTPUT,@ID VARCHAR(20) OUTPUT
AS
SET @ID=(SELECT TOP 1 S# FROM S)
IF @ID IS NULL
BEGIN
SET @ID='不存在ID'
END
SELECT @STATUS=CASE WHEN @ID IS NULL THEN 0 ELSE 1 ENDDECLARE @status INT,@ID VARCHAR(20)
EXECUTE P_CheckCPXY @status OUTPUT ,@ID OUTPUT
SELECT @status 状态,@ID ID
/*
状态 ID
----------- --------------------
1 040405028 (1 行受影响)*/DROP PROCEDURE P_CheckCPXY
AS
BEGIN
SET @ID=(SELECT TOP 1 S# FROM S )
SELECT @STATUS=CASE WHEN @ID IS NULL THEN 0 ELSE 1 END
IF @ID IS NULL
BEGIN
SET @ID='不存在ID'
END
END;
---测试
DECLARE @status INT,@ID VARCHAR(20)
EXECUTE P_CheckCPXY @status OUTPUT ,@ID OUTPUT
SELECT @status 状态,@ID ID
/*
状态 ID
----------- --------------------
1 040405028 (1 行受影响)*/DROP PROCEDURE P_CheckCPXY