Practical bank transfer stored procedures and serial number generation stored procedures _mssql

Source: Internet
Author: User
Tags getdate rollback

Bank Transfer stored procedures

Use [bankinfor]
go
set ANSI_NULLS on Go Set QUOTED_IDENTIFIER on Go
ALTER PROCEDURE [dbo].[ Transfer] (@inAccount int, @outAccount int, @amount float) as
declare
  @totalDeposit float;
  The Begin
  Select @totalDeposit =total from the account where accountnum= @outAccount;
  If @totalDeposit is null
  begin
  rollback;
  print ' Turn out the account does not exist or there is no deposit in the account ' return
  ;
  End
  If @totalDeposit < @amount
  begin
  rollback;
  print ' balance not enough to operate ' return
  ;
  End
  Update account set total=total-@amount where accountnum= @outAccount;
  Update account set total=total+ @amount where accountnum= @inAccount;
  print ' Transfer successful! '
  commit;
  End

Stream number generation stored procedure

if exists (select 1 from sysobjects where id=object_id (' Getserialno ') and Xtype= ' P ") drop proc Getserialno go Create proce dure [dbo].  
[Getserialno] (@sCode varchar) as begin Declare @sValue varchar (), @dToday datetime, @sQZ varchar (50)-This represents the prefix Begi N Tran Begin Try-Lock the record, a lot of people lock it, start with an update--in the same thing, the lock update Serialno SE is started after the UPDATE statement is executed.  T svalue=svalue where scode= @sCode Select @sValue = svalue from Serialno where scode= @sCode Select @sQZ = Sqz from Serialno where scode= @sCode--there is no record in the factor table, insert the initial value If @sValue is null Begin Select @sValue = CONVERT (bigin T, convert (varchar (6), GETDATE (), () + ' 000001 ') Update Serialno set svalue= @sValue where scode= @sCode end Els The Select @dToday = substring (@sValue, 1,6)--If the date is equal, add 1 if @dToday = Convert  
     (varchar (6), GETDATE (), 12)    Select @sValue = CONVERT (varchar) (CONVERT (bigint, @sValue) + 1) Else   -If the date is not equal, the first assignment date, the serial number starting from 1 Select @sValue = CONVERT (bigint, CONVERT (varchar (6), GETDATE (),) + ' 000001 ') Upd   
  Ate Serialno Set svalue = @sValue where scode= @sCode end Select result = @sQZ + @sValue Commit Tran End Try Begin Catch Rollback Tran Select result = ' Error ' End Catch end select*from Serialno Select CONVERT (v Archar (6), GETDATE (), 12) + ' 000001 '

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.