Build a temporary table from a stored which will be used for reporting.
How do you invoke the temporary table
ie (#MGFINAL ) to be used in the
dataset .
Thanks.
Using
visual studio 2010
USE [Credit_App]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[rpt_detail_statement]
(
@ZIDNO VARCHAR(20),
@ZLOANUMBER VARCHAR(20),
@ZINT_CODE VARCHAR(20)
)
As
Begin
CREATE TABLE #MGSTART
(
[IDNO] [varchar](20) ,
[LOANUMBER] [varchar](20) ,
[INT_NO] [varchar](20) ,
[INT_NAME] [varchar](80) ,
[TERM_START] [numeric](18, 0) ,
[TX_DATE] [datetime] ,
[PERIOD] [numeric](18, 0) ,
[YEAR1] [numeric](18, 0) ,
[OPENING] [numeric](18, 2) ,
[PAYMENT] [numeric](18, 2) ,
[INTEREST] [numeric](18, 2) ,
[REPAY] [numeric](18, 2) ,
[REDEMP] [numeric](18, 2) ,
[REPSUM] [numeric](18, 2) ,
[CLOSING] [numeric](18, 2) ,
)
CREATE TABLE #MGFINAL(
[IDNO] [varchar](20) ,
[LOANUMBER] [varchar](20) ,
[INT_NO] [varchar](20) ,
[INT_NAME] [varchar](80) ,
[TERM_START] [numeric](18, 0) ,
[TX_DATE] [datetime] ,
[PERIOD] [numeric](18, 0) ,
[YEAR1] [numeric](18, 0) ,
[OPENING] [numeric](18, 2) ,
[PAYMENT] [numeric](18, 2) ,
[INTEREST] [numeric](18, 2) ,
[REPAY] [numeric](18, 2) ,
[REDEMP] [numeric](18, 2) ,
[REPSUM] [numeric](18, 2) ,
[CLOSING] [numeric](18, 2) ,
[TOT_RED] [numeric](18, 2) ,
[ACT_REPAYMENT] [numeric](18, 2)
)
CREATE TABLE #MGREDEMP(
[IDNO] [varchar](20) ,
[INT_NO] [varchar](20) ,
[INT_NAME] [varchar](80) ,
[PERIOD] [numeric](18, 2) ,
[YEAR1] [numeric](18, 2) ,
[TOT_RED] [numeric](18, 2) ,
[CLOSING] [numeric](18, 2) ,
[LOANUMBER] [varchar](8)
)
INSERT INTO #MGSTART
SELECT IDNO ,LOANUMBER ,INT_NO ,[INT_NAME] ,
[TERM_START],[TX_DATE] ,[PERIOD] ,[YEAR1] ,
[OPENING] ,[PAYMENT] ,[INTEREST],[REPAY] ,
[REDEMP] ,[REPSUM] ,[CLOSING]
FROM MGBALANCE WHERE IDNO=@ZIDNO AND LOANUMBER=@ZLOANUMBER AND INT_NO=@ZINT_CODE
INSERT INTO #MGREDEMP
SELECT [IDNO] , [INT_NO] , [INT_NAME] , [PERIOD] ,
[YEAR1] , SUM(PAY_AMT) TOT_RED ,
MIN(POST_BALANCE) CLOSING ,
[LOANUMBER]
FROM REDEMP WHERE IDNO=@ZIDNO AND LOANUMBER=@ZLOANUMBER AND INT_NO=@ZINT_CODE
GROUP BY IDNO , INT_NO ,INT_NAME,[PERIOD] ,[YEAR1] , [LOANUMBER]
INSERT INTO #MGFINAL
SELECT #MGSTART.IDNO ,#MGSTART.LOANUMBER,
#MGSTART.INT_NO ,#MGSTART.INT_NAME ,
#MGSTART.TERM_START,#MGSTART.TX_DATE , #MGSTART.PERIOD , #MGSTART.YEAR1,
#MGSTART.OPENING ,#MGSTART.PAYMENT , #MGSTART.INTEREST, #MGSTART.REPAY,
#MGSTART.REDEMP ,#MGSTART.REPSUM , 0 ,
#MGREDEMP.CLOSING ,#MGREDEMP.TOT_RED
FROM #MGSTART
LEFT JOIN #MGREDEMP ON #MGSTART.IDNO=#MGREDEMP.IDNO AND
#MGSTART.PERIOD=#MGREDEMP.PERIOD AND
#MGSTART.YEAR1=#MGREDEMP.YEAR1
SELECT * FROM #MGFINAL
End