使用临时表构建存储过程的数据集以报告.rdlc
问题描述：
从存储中构建临时表，用于报告。
如何调用临时表即（#MGFINAL）用于数据集。
谢谢。
使用 visual studio 2010
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
/****** Object: StoredProcedure [dbo].[rpt_detail_statement] Script Date: 09/19/2014 11:49:13 ******/
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
答
无论是从临时表或视图还是用户表中检索数据，您需要做的就是设置命令t ype作为存储过程。
It doesn’t matter whether you are retrieving data from temp table or views or user table, everything you have to do is set the command type as a stored procedure.
//Declare connection
using (SqlConnection con = new SqlConnection("YourConnecectionString"))
{
//Declare data adapter
using (SqlDataAdapter da = new SqlDataAdapter("[dbo].[rpt_detail_statement]", con))
{
//Set command type as stored procedure
da.SelectCommand.CommandType = CommandType.StoredProcedure;
//Add parameter
da.SelectCommand.Parameters.Add("@ZIDNO", SqlDbType.VarChar, 20).Value = "Value For ZIDNO";
da.SelectCommand.Parameters.Add("@ZLOANUMBER", SqlDbType.VarChar, 20).Value = "Value For ZLOANUMBER";
da.SelectCommand.Parameters.Add("@ZINT_CODE", SqlDbType.VarChar, 20).Value = "Value For ZINT_CODE";
//Declare dataset
using (DataSet ds = new DataSet())
{
//Fill dataset
da.Fill(ds);
//use this ds here
}
}
}
As PhilLenoir said, you can do this way also
//Declare connection
using (SqlConnection con = new SqlConnection("YourConnecectionString"))
{
//Declare data adapter
using (SqlDataAdapter da = new SqlDataAdapter("EXEC [dbo].[rpt_detail_statement] @ZIDNO,@ZLOANUMBER,@ZINT_CODE", con))
{
//No need to set command type as stored procedure
//Add parameter
da.SelectCommand.Parameters.Add("@ZIDNO", SqlDbType.VarChar, 20).Value = "Value For ZIDNO";
da.SelectCommand.Parameters.Add("@ZLOANUMBER", SqlDbType.VarChar, 20).Value = "Value For ZLOANUMBER";
da.SelectCommand.Parameters.Add("@ZINT_CODE", SqlDbType.VarChar, 20).Value = "Value For ZINT_CODE";
//Declare dataset
using (DataSet ds = new DataSet())
{
//Fill dataset
da.Fill(ds);
//use this ds here
}
}
}