向一个表里插入一条记录,
如果该记录ID已经存在
[email protected]
否则,插入记录
------解决方案--------------------
create table 表(id int)
create proc up_test(@id int,@cz int output)
as
if not exists(select 1 from 表 where [email protected])
begin
insert into 表
select @id
end
else
begin
set @[email protected]
print @cz
end
declare @cz int
exec up_test 1,@cz output
------解决方案--------------------
create trigger insert_trg
instead of 表
as
begin
if exists(select * from 表 where id=inserted.id)
print '存在 '
else
insert into 表 select .....
end
------解决方案--------------------
Create Proc Test
@ID int
AS
BEGIN
If Exists(Select * From 表名 WHere [email protected])
Return 1
Else
Insert Into 表名 .......
Return 0
END
Declare @cz int,@ID int
Select @ID=1,@cz=0
EXEC @cz=test,@ID
Select @cz
------解决方案--------------------
写错了,应该是这样
Declare @cz int,@ID int
Select @ID=1,@cz=0
EXEC @cz=test @ID
Select @cz
------解决方案--------------------
用触发器是最明智的选择~存储过程稍显麻烦~
create trigger tri_comp
instead of [table]
as
if exists(select * from [table] where id=inserted.id) --判断是否有相同ID
print '该记录已存在 '
else
insert into [table] select * from inserted