SQL Functions Examples

SQL Functions Examples

ALTER function CalculateBPL
(
@TradingAccount varchar(15),
@SellPrice Decimal(24,6),
@BuyPrice Decimal(24,6),
@BuySellQty int
)
Returns Decimal(24,6)
AS
BEGIN
Declare @BPL decimal(24,6)
If @TradingAccount = ‘PTST’
Set @BPL = 0
Else
Set @BPL = (@SellPrice-@BuyPrice) * ISNULL(@BuySellQty ,0)
Return @BPL
END

ALTER Function dbo.getOrderStatus (@BrokerTranID as varchar(15))
Returns varchar(25)
As
begin
Declare @OrderStatus varchar(25)
Set @OrderStatus=”

If Exists(select * from TransactionLog where TranId=@BrokerTranId AND OrderTraded=1)
Set @OrderStatus=‘TRADED’
Else
If Exists(select * from V_PendingOrderMIS where BrokerTranId=@BrokerTranId)
Set @OrderStatus=‘PENDING’
Else
select @OrderStatus=OrderStatus from V_Clientactivitymis where BrokerTranId=@BrokerTranId

return @OrderStatus

End

ALTER Function FN_FindClients(@DealerCode varchar(50), @Level Int )
Returns @treemap table (ClientCode varchar(50))
AS
Begin
Declare @DealerCode_Sub Varchar(50),
@StrDealerCursor Varchar(5000)

Insert @treemap
Select ClientCode FROM DealerClientMaster WHERE DealerCode = @DealerCode

Set @Level = @Level + 1
If @Level = 1
Begin
DECLARE Dealer_Cursor1 CURSOR FOR
SELECT ClientCode FROM DealerClientMaster WHERE DealerCode= @DealerCode And ClientCode In (Select DealerCode From DealerClientMaster)
OPEN Dealer_Cursor1

FETCH FROM Dealer_Cursor1 Into @DealerCode_Sub
WHILE @@FETCH_STATUS = 0
BEGIN
Insert into @Treemap SELECT * FROM FN_FindClients(@DealerCode_Sub, @Level)
FETCH NEXT FROM Dealer_Cursor1 Into @DealerCode_Sub
END

CLOSE Dealer_Cursor1
DEALLOCATE Dealer_Cursor1
End
Return
End

ALTER function treemap ()
returns @treemap table (
map varchar(200)
)
as
BEGIN

insert @treemap
select distinct dealercode from dealerclientmaster
where dealercode in(select clientcode from dealerclientmaster) or
dealercode not in(select clientcode from dealerclientmaster)
Return
End

– Select * from dbo.GetM2M(‘vishalbh’)
ALTER Function GetM2M(@ClientCode varchar(15),@isOpt bit)
Returns @M2M table (
M2M decimal(24,6),BPL decimal(24,6),ProductType varchar(10)
)
AS
Begin
If @isOpt =0
Begin
Insert Into @M2M
Select IsNull(SUM(M2M),0),IsNull(SUM(BPL),0),dbo.getProduct(Scrip)
From m2mbpldetails With (NOLOCK)
Where ClientCode = @ClientCode And dbo.getProduct(Scrip) <> ‘OPTION’
Group by ClientCode,dbo.getProduct(Scrip)
End

If @IsOpt =1
Begin
Insert Into @M2M
Select IsNull(SUM(M2M),0),IsNull(SUM(BPL),0),
Case BuySellINdicator When ‘B’ then ‘OPTBUY’ when ‘S’ then ‘OPTSELL’ end
From m2mbpldetails With (NOLOCK)
Where ClientCode = @ClientCode And DBO.getProduct(Scrip)= ‘OPTION’
Group by ClientCode,dbo.getProduct(Scrip),BUySellINdicator
End

Update @M2M Set M2M = isnull(M2M,0),BPL = ISNULL(BPL,0)
Delete From @M2M where ProductType is NULL
RETURN

End