使用临时表构建存储过程的数据集以报告.rdlc [英] Building a data set out of a stored procedure with a temporary table for reporting .rdlc
本文介绍了使用临时表构建存储过程的数据集以报告.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
}
}
}
这篇关于使用临时表构建存储过程的数据集以报告.rdlc的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文