Home > SQL Server > Function Table and CURSOR SQL SERVER

Function Table and CURSOR SQL SERVER

CREATE FUNCTION [dbo].[TB_BBOOKS]
(
@ReportDate smalldatetime,@acc_type varchar(30)
)
RETURNS @RETURN TABLE
(
CATEGORY varchar(30),AUD float,CHF float,EUR float,GBP float,HKD float,
IDR float,JPY float,SGD float,THB float,USD float
)
AS
BEGIN
DECLARE @PORTFOLIO_ID int
DECLARE @SUM_AMOUNT float
DECLARE @CURRENCY_ID char(3)

DECLARE @AUD float
DECLARE @CHF float
DECLARE @EUR float
DECLARE @GBP float
DECLARE @HKD float
DECLARE @IDR float
DECLARE @JPY float
DECLARE @SGD float
DECLARE @THB float
DECLARE @USD float

DECLARE ACursor CURSOR FOR
SELECT PORTFOLIO_ID FROM TYPE_ACOUNT WHERE TYPE_ACOUNT=@acc_type
OPEN ACursor
— Perform the first fetch.
FETCH NEXT FROM ACursor INTO @PORTFOLIO_ID
— Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

SET @AUD = 0
SET @CHF = 0
SET @EUR = 0
SET @GBP = 0
SET @HKD = 0
SET @IDR = 0
SET @JPY = 0
SET @SGD = 0
SET @THB = 0
SET @USD = 0

DECLARE ACursor2 CURSOR FOR
SELECT CURRENCY_ID FROM CURRENCY ORDER BY CURRENCY_ID
OPEN ACursor2
FETCH NEXT FROM ACursor2 INTO @CURRENCY_ID
— Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PORTFOLIO_ID=26
BEGIN
SELECT @SUM_AMOUNT=(-1*sum(ABS(AMOUNT))) FROM MM_DEALS_BBOOKS WHERE PORTFOLIO_ID in (13,15,26)
AND CASH_FLOW_DATE>@ReportDate and REPORT_DATE<=@ReportDate
AND CURRENCY_ID=@CURRENCY_ID
END ELSE BEGIN
SELECT @SUM_AMOUNT=sum(AMOUNT) FROM MM_DEALS_BBOOKS WHERE PORTFOLIO_ID=@PORTFOLIO_ID
AND CASH_FLOW_DATE>@ReportDate and REPORT_DATE<=@ReportDate
AND CURRENCY_ID=@CURRENCY_ID
END

IF @CURRENCY_ID=’AUD’
BEGIN
SET @AUD = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’CHF’
BEGIN
SET @CHF = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’EUR’
BEGIN
SET @EUR = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’GBP’
BEGIN
SET @GBP = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’HKD’
BEGIN
SET @HKD = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’IDR’
BEGIN
SET @IDR = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’JPY’
BEGIN
SET @JPY = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’SGD’
BEGIN
SET @SGD = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’THB’
BEGIN
SET @THB = @SUM_AMOUNT
END
ELSE
IF @CURRENCY_ID=’USD’
BEGIN
SET @USD = @SUM_AMOUNT
END
FETCH NEXT FROM ACursor2 INTO @CURRENCY_ID
END
CLOSE ACursor2
DEALLOCATE ACursor2

INSERT @Return values (dbo.getPORTFOLIONAME(@PORTFOLIO_ID),@AUD ,@CHF ,@EUR ,@GBP,@HKD ,
@IDR ,@JPY ,@SGD ,@THB ,@USD)

— Perform the first fetch.
FETCH NEXT FROM ACursor INTO @PORTFOLIO_ID
END
CLOSE ACursor
DEALLOCATE ACursor
RETURN
END

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: