Building a data set out of a stored procedure with a temporary table for reporting .rdlc

Question

0.00/5 (No votes)

See more:

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

Posted 19-Sep-14 2:34am

Member 10744248

Add a Solution

Comments

PhilLenoir 19-Sep-14 9:02am

How are you setting up your dataset? The SelectCommand of your dataset should be:
EXEC rpt_detail_statement @ZIDNO, @ZLOANNUMBER, @ZINT_CODE
With select parameters: ZIDNO, ZLOANNUMBER and ZINT_CODE which you'll have to supply values for.

Member 10744248 19-Sep-14 9:17am

I am building in the data set table adapter

Member 10744248 19-Sep-14 9:18am

using visual studio 2010

Member 10744248 19-Sep-14 9:46am

Where is the SelectCommand Please

Member 10744248 19-Sep-14 10:26am

Please assist step by step.

New

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abdul Samad KP · Answer 1 · 2014-09-19T09:10:00

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.

C#

//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

C#

//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
                    }
                }
            }

Building a data set out of a stored procedure with a temporary table for reporting .rdlc

1 solution

Solution 1

Add your solution here

Preview 0

Building a data set out of a stored procedure with a temporary table for reporting .rdlc

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us