sql server中存储过程

--阶段二   编写带参数的存储过程

--创建存储过程，实现某账户使用一卡通登录的过程，验证次一卡通账号并输出获得的账户金额

use Bank
go

create procedure checkLoginMess
  
   @card_id varchar(20),
   @card_password char(6),
   @putOutBalance  float output

 as
   begin
     --定义正确用户的卡号和密码
     declare @true_id varchar(20)
     declare @true_password char(6)
     
     declare @username varchar(64)='陆逊'
     --declare @balandce float
     
     select @true_id=card_id,@true_password=CARD_PASSWORD from ALL_PURPOSE_CARD 
     where ACCOUNT_ID=(select ACCOUNT_ID from ACCOUNT where ACCOUNT_NAME=@username)
     
     --进行判断
     if @card_id=@true_id and @card_password=@true_password
        begin
           select @putOutBalance=balance from ALL_PURPOSE_CARD where ACCOUNT_ID=(
           select ACCOUNT_ID from ACCOUNT where ACCOUNT_NAME=@username)
        end
      else 
        print '用户名和密码验证出错！'
   end
   
 go
 
 
 --调用存储过程
 
 --定义一个余额，用于存放存储过程输出的余额   注意添加output选项
 
 declare @balance float
 exec checkLoginMess '6225887706549011','111111',@balance output
 select @balance '余额'
 

--使用存储过程，通过用户信用卡的卡号和月份，实现某用户的信用卡在某月份的交易记录

use Bank
go

create procedure getCreditExchange
(
  @credit_no varchar(16),
  @month int
)
as
  begin
    select * from CREDIT_EXCHANGE where CREDIT_CARD_ID=(
    select CREDIT_CARD_ID from CREDITCARD where CREDIT_CARD_NO=@credit_no and
    MONTH(extime)=@month)
  end
go

--调用存储过程
exec getCreditExchange '3568427475946349',10