SQL Stored Procedures

Global Variables:

@@Error
@@RowCount
@@transaction
@@identity
If there is a bug in Insert, Update, delete then @@rowcount returns ZERO.

@@error will have value ZERO if there are no bugs in Insert, Update, delete.

ALTER Procedure dbo.AddToTradeConsolidation
(
@TranValues Varchar(7000),
@ErrorCode Int,
@Message Varchar(200)
)
AS
Begin
Insert Into ConfirmationMissing (Type, TranValues, ErrorCode, Message, TranTime) Values (‘Trade’,@TranValues,@ErrorCode, @Message, GetDate())

End

ALTER Procedure GetTradeDetails
AS
BEGIN
SET NOCOUNT ON

Select Exchange, convert(varchar(10),tradetime,121) as Tradedate, Count(*) As NoOfTrades, Sum(Isnull(Quantity,0) * Isnull(Price,0) ) As TradeValue From TradeDetails Group by Exchange, convert(varchar(10),tradetime,121) order By Exchange

END

ALTER Procedure OREGetBankDetails
( @SessionNo varchar(25),
@Introductoryid varchar(15),
@source tinyint,
@Clientcode varchar(25),
@BankAdd varchar(100) output,
@BankAccNo varchar(15) output,
@DpAcc varchar(15) output,
@ITPan varchar(15) output,
@BankName varchar(100) output,
@AnnualIncome decimal(12,2) output,
@DateOfAssociation datetime output,
@Remarks varchar(100) output,
@hpin varchar(15)output,
@tpin varchar(15) output,
@Errorcode int output ,
@Message varchar(100) output
)

AS
BEGIN
If @Source Is Null or @Source >4 or
@SessionNo Is Null or @SessionNo =” or
@Clientcode Is null Or @ClientCode =” Or @IntroductoryID Is Null Or @IntroductoryID =”
Begin
Set @Errorcode=-2002001
Exec OREGetErrorMessage @Errorcode, @Message output
Return
End

/* validating login id*/
Exec OREValidateIntroductoryLogin @IntroductoryID,@Source,@SessionNo,@ErrorCode output, @Message output
If @ErrorCode <> 0
Begin
Return
End

If Not Exists(Select clientcode from clientmaster where clientcode=@clientcode)
Begin
Set @Errorcode=-2002009
Exec OREGetErrorMessage @errorcode, @Message output
Return
End
Else
Begin
Select @BankAdd=BankAddress, @BankAccNo=BankAccountno, @DpAcc=DpAccountno, @ITPan=IncometaxPan, @BankName=BankName, @AnnualIncome=AnnualIncome, @DateOfAssociation=DateOfAssociation, @Remarks=Remarks, @hpin=hpin, @tpin=tpin From ClientMaster Where ClientCode=@ClientCode
If @@Error <> 0 Or @@RowCount <> 1
Begin
Set @ErrorCode = -99999
Set @message = ‘Bank Information for Client could not be found’
Return
End
End

Set @Errorcode= 0
Set @Message= ”
Return

END

ALTER Procedure AddOverDraftLimit
(
@SessionNo varchar(25) ,
@Source TinyInt ,
@DealerCode Varchar(15) ,
@ClientCode Varchar(15),
@Amount Decimal(24,2),
@ErrorCode int output,
@Message varchar(200) output
)

AS
BEGIN
SET NOCOUNT ON
Declare @LoginName varchar(15),
@OverDraftUsedLimit decimal(24,4),
@OverDraftLimit decimal(24,4),
@AvailableOverDraftLimit decimal(24,4),
@nRow int,
@nError int
Set @Amount = ISNULL(@Amount,0)
If @SessionNo Is Null OR @SessionNo =” OR
@Source <> 0 OR
@DealerCode is Null or @DealerCode =” OR
@ClientCode is Null or @ClientCode =” OR
@Amount < 0

Begin
Set @ErrorCode = -120012
Set @Message =‘Wrong parameters are passed’
Return
End

Set @LoginName = Ltrim(Rtrim(Substring(@SessionNo,1,15)))

–Validate Dealer’s Login
Exec OREValidateClientLogin @LoginName,‘ALL’,‘ALL’,@Source,”,@SessionNo,@ErrorCode OUTPUT,@Message OUTPUT
If @ErrorCode <> 0
Begin
Return
End

–validate dealer client relationship
If Not Exists(Select DealerCode From DealerClientmaster Where DealerCode= @DealerCode And ClientCode = @ClientCode)
Begin
Set @ErrorCode = -120013
Set @Message =‘Client ‘ + @ClientCode + ‘ is not assigned to dealer ‘ + @DealerCode
Return
End

–validate Overdraft limit in dealers account
Select @OverDraftUsedLimit = OverDraftUsedLimit, @OverDraftLimit = OverDraftLimit
From Dealermaster WITH(NOLOCK) Where ClientCode = @DealerCode
Select @nRow = @@Rowcount ,@nError = @@Error

If @nRow <> 1
Begin
Set @ErrorCode = -120014
Set @Message = ‘Dealer code ‘ + @DealerCode + ‘ does not exist in the system’
Return
End

If @nError <> 0
Begin
Set @ErrorCode = -120014
Set @Message = ‘Error While selecting data for ‘ + @DealerCode
Return
End

Set @OverDraftUsedLimit = ISNULL(@OverDraftUsedLimit,0)
Set @OverDraftLimit = ISNULL( @OverDraftLimit,0)

Set @AvailableOverDraftLimit = @OverDraftLimit – @OverDraftUsedLimit

If @AvailableOverDraftLimit < @Amount
Begin
Set @ErrorCode = -120015
Set @Message = ‘Dealer does not have enough overdraft Limit to distribute ‘
Return
End

begin Transaction
Save Transaction Adhoc
Update DealerMaster Set OverDraftUsedLimit = OverDraftUsedLimit + @Amount
Where ClienTCode = @DealerCode
If @@Rowcount <> 1 or @@Error <> 0
Begin
Rollback Transaction Adhoc
Commit Transaction
Set @ErrorCode = -120018
Set @Message = ‘Error while updating clients record.Transaction is rolled back’
Return
End

Update ClientCashMaster
Set CashDeposited = CashDeposited + @Amount ,
GrossAllocatedExposure = GrossAllocatedExposure +(@Amount * GrossExpMultiplier),
NetAllocatedExposure = NetAllocatedExposure +(@Amount * NetExpMultiplier)
Where ClientCode = @ClientCode
If @@Rowcount <> 1 or @@Error <> 0
Begin
Rollback Transaction Adhoc
Commit Transaction
Set @ErrorCode = -120017
Set @Message = ‘Error while updating clients record.Transaction is rolled back’
Return
End
Commit Transaction
Set @ErrorCode =0
Set @message = ‘Adhoc cash updated succesfully to the account of ‘+ @Clientcode
return
END
——————————————————————————————————-
ALTER Procedure ORECheckSession
( @SessionNo varchar(25),
@ClientCode varchar(50), — THIS IS ACTUALLY lOGINNAME
@Source tinyint ,
@ErrorCode int output,
@Message varchar(200) output)
–WITH ENCRYPTION

AS
BEGIN
declare

@CSessionNo varchar(25), @CClientStatus bit ,@CSource tinyint
Select @CSessionno = SessionNo,@CClientStatus = ClientStatus,@CSource = Source From UserLogin Where ClientCode = @ClientCode or loginname = @ClientCode –and Source = @Source

If @@Error <> 0 or @@Rowcount <> 1
Begin
Set @ErrorCode = -999
Set @Mssage =‘Your Session has expired. Please try again..’
Return
End

If ((ltrim(rtrim(isnull(@CSessionno,”))) <> ltrim(rtrim(@SessionNo))) or @CClientStatus = 0)
Begin
Set @ErrorCode = -999
Set @Message =‘Your Session is expired. Please login again….’
Return
End

If dbo.CheckSource(@Source ,@CSource) <> 0
Begin
Set @ErrorCode = -999
Set @Message =‘You do not have access to the logged in source ‘
Return
End

Set @ErrorCode = 0
Set @Message =‘Success’

Return
END