sqlserver判断存储过程是否存在，存在则更新，否则插入
create PROCEDURE proName
@name NVARCHAR(50),
@O_RETURN INT OUTPUT,
@Id INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN T --创建事务
DECLARE @AJIEGUO INT
--判断是否存在
IF EXISTS(SELECT* from table WHERE name = @name)
BEGIN
UPDATE table SET name = @name, WHERE name = @name
IF(@@ROWCOUNT>0)
begin
SET @O_RETURN=1
set @Id=(SELECT Id from table WHERE name = @name) --获取更新的的id
end
else
begin
SET @O_RETURN=0
end
END
ELSE
BEGIN
INSERT INTO table
(
name
)
VALUES
(
@name
)
IF(@@ROWCOUNT>0)
begin
SET @O_RETURN=1
set @Id=@@identity --取到刚插入的id
end
else
begin
SET @O_RETURN=0
end
END
END TRY
BEGIN CATCH --捕捉错误
SET @AJIEGUO=@@ERROR;
END CATCH
IF(@AJIEGUO=0)
BEGIN
COMMIT TRAN T; --提交事务
SET @O_RETURN =1;
END
ELSE
BEGIN
ROLLBACK TRAN T;--回滚事务
SET @O_RETURN =0;
END
END
