dotnet- T-SQL、存储过程和系统变量(备忘)

T-SQL 是SQL server 对SQL的拓展

实例1：

 

declare @i int =1
declare @sum int = 0
while @i<=100
begin
set @sum = @sum + @i
set @i = @i +1
end
select @i as i,@sum as sum

 

实例2 ：

结合存储过程写的转账：uprocedure

create procedure usp_TransferMoney

@from int ,

@to int,

@moneyCount float,

@ResultCode int out -- 1 success 2,yu e bu zu 3shibai

as

begin

declare @balanceMoney float

set @balanceMoney = (select balance from T_Bank where id =@from)

if (@balanceMoney -@moneyCount)<= 10

begin

 set @ResultCode = 2

end

else 

begin

 begin transaction

 declare @sumError int =0

 update T_Bank set balance = balance - @moneyCount where id = @from

 set @sumError = @sumError + @@ERROR

 update T_Bank set balance = balance + @moneyCount where id = @to

 set @sumError = @sumError + @@ERROR

 if @sumError != 0

 begin

 set @ResultCode = 3

 rollback transaction

 end

 else

 begin

 set @ResultCode = 1

 commit transaction

 end

end

end

 

调用存储过程
declare @result int 
exec usp_TransferMoney 0,4,100,@result output
select @result;

 

查看存储过程的SQL：

@@error 系统变量

exec sp_helptext usp_TransferMoney

 

常用的全局变量：