使用临时表构建存储过程的数据集以报告.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

推荐答案

//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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！