sqlserver存储过程

sqlserver存储过程

--存储过程
CREATE PROC testPROC
	@a INT, --输入参数按值传递
	--@a INT = 20, --可以给默认值
	@b INT OUTPUT --输出参数按引用传递
AS
	PRINT '传进来的a = ' + CAST(@a AS VARCHAR(10))
	PRINT '传进来的b = ' + CAST(@b AS VARCHAR(10))
SET @a = @a + 6
SET @b = @b + 6
	PRINT '修改后的a = ' + CAST(@a AS VARCHAR(10))
	PRINT '修改后的b = ' + CAST(@b AS VARCHAR(10))
GO
--调用存储过程
--调用存储过程的时候，使用EXECUTE关键字，可以简写成EXEC
DECLARE @c INT
DECLARE @d INT
SET @c = 10
SET @d = 10
EXECUTE testPROC @a=@c, @b=@d OUTPUT
PRINT @c
PRINT @d
GO
--调用存储过程
DECLARE @c INT
DECLARE @d INT
SET @c = 30
SET @d = 30
EXEC testPROC @b=@d OUTPUT --存储过程中有默认值的参数可以不传(所以我这里调用时只传了一个参数)
PRINT @c
PRINT @d
GO
--

--
SELECT * FROM bank
GO
--创建存储过程
CREATE PROC myTRANSFER
	@inId int,
	@outId int,
	@money float,
	@inIdMoney float OUTPUT,--输出参数
	@outIdMoney float OUTPUT --输出参数
AS
UPDATE bank SET currentMoney = currentMoney + @money WHERE cid = @inId
UPDATE bank SET currentMoney = currentMoney - @money WHERE cid = @outId
SELECT @inIdMoney = currentMoney FROM bank WHERE cid = @inId
SELECT @outIdMoney = currentMoney FROM bank WHERE cid = @outId
PRINT '转进后的金额=' + CAST(@inIdMoney AS VARCHAR(10))
PRINT '转出后的金额=' + CAST(@outIdMoney  AS VARCHAR(10))
GO

--调用存储过程
DECLARE @a FLOAT
DECLARE @b FLOAT
--调用存储过程的时候，使用EXECUTE关键字，可以简写成EXEC
EXEC myTRANSFER @inId=10,@outId=11,@money=100,@inIdMoney=@a OUTPUT,@outIdMoney=@b OUTPUT
PRINT '(调用存储过程-输出参数)转进后的金额=' + CAST(@a AS VARCHAR(10))
PRINT '(调用存储过程-输出参数)转出后的金额=' + CAST(@b AS VARCHAR(10))
GO

SELECT * FROM bank
GO