34,590
社区成员
发帖
与我相关
我的任务
分享
create proc p_test
as
begin
begin try
begin tran
insert ...
update ...
commit
select 1
end try
begin catch
rollback
select 0
end catch
SELECT 1
PRINT 1
SELECT @@ROWCOUNT
/*
-----------
1
(1 個資料列受到影響)
-----------
0
(1 個資料列受到影響)
*/
@@rowcount 是記錄行數,可以轉換成bit類型:
Select Cast(@@Rowcount As bit) As Return
USE test
go
------------------------------------------------------------------ Create -----------------------------
IF object_id('tb_test','u')IS NOT NULL
DROP TABLE tb_test
Go
CREATE TABLE tb_test(ID INT PRIMARY KEY)
Go
IF object_id('pr_test','p')IS NOT NULL
DROP PROCEDURE pr_test
Go
CREATE PROCEDURE pr_test(
@ID INT
)
AS
IF NOT EXISTS(SELECT 1 FROM tb_test WHERE ID=@ID)
INSERT INTO tb_test(ID)
VALUES(@ID)
SELECT @@ROWCOUNT AS [Return]
GO
------------------------------------------------------------------ Execute -----------------------------
DECLARE @flag BIT
-- first
EXEC pr_test @ID=1
-- second
EXEC pr_test @ID=1
/*
first_Return
-----------
1
second_Return
-----------
0
*/
USE test
go
------------------------------------------------------------------ Create -----------------------------
IF object_id('tb_test','u')IS NOT NULL
DROP TABLE tb_test
Go
CREATE TABLE tb_test(ID INT PRIMARY KEY)
Go
IF object_id('pr_test','p')IS NOT NULL
DROP PROCEDURE pr_test
Go
CREATE PROCEDURE pr_test(
@Return BIT OUTPUT
,@ID INT
)
AS
IF NOT EXISTS(SELECT 1 FROM tb_test WHERE ID=@ID)
INSERT INTO tb_test(ID)
VALUES(@ID)
SET @Return=@@ROWCOUNT
GO
------------------------------------------------------------------ Execute -----------------------------
DECLARE @flag BIT
-- first
EXEC pr_test @Return=@flag OUTPUT,@ID=1
SELECT ID AS first_ID FROM tb_test
SELECT @flag AS first_flag
SET @flag=NULL
-- second
EXEC pr_test @Return=@flag OUTPUT,@ID=1
SELECT ID AS second_ID FROM tb_test
SELECT @flag AS second_flag
/*
first_ID
-----------
1
first_flag
----------
1
second_ID
-----------
1
second_flag
-----------
0
*/