CODEWITHSUNDEEP
sql-serverdatabase
Aminur Rahman
Aminur Rahman

Reputation: 410

HOW TO CREATE TEMP TABLE USING stored procedure

I want to create a stored procedure in SQL Server 2012 that returns a temp table.

My code is

CREATE PROC [dbo].[aac_trial_balance_data]
    @company_code char(5),
    @target_level int,
    @StartDate char(12),
    @EndDate char(12)
AS
BEGIN
    SELECT
        dbo.getParentCode(chart_code,@target_level,LEVEL) chart_code,
        level,
        SUM(debit) debit, 
        SUM(credit) credit
    FROM
        acc_trial_balance_vw 
    WHERE
        convert(datetime, create_date, 103) between convert(datetime, cast(@StartDate as datetime), 103) 
                                                and convert(datetime, cast(@EndDate as datetime) + '23:59:59', 103)  
        AND company_code = @company_code 
    GROUP BY 
        chart_code, LEVEL 
END

I want to create a Temp table after the query like 

CREATE PROC [dbo].[aac_trial_balance_data]
    @company_code char(5),
    @target_level int,
    @StartDate char(12),
    @EndDate char(12)
AS
BEGIN
    (select 
            dbo.getParentCode(chart_code,@target_level,LEVEL) chart_code,
            level,
            SUM(debit) debit, 
            SUM(credit) credit
        from acc_trial_balance_vw 
        where 
        convert(datetime,create_date,103) between convert(datetime, cast(@StartDate as datetime) , 103) 
        and  convert(datetime, cast(@EndDate as datetime)+'23:59:59' , 103)  
        and company_code = @company_code 
         GROUP BY chart_code, LEVEL 
         )
         AS
         #TEMP-TABLE -- This is my Temp Table That i want to create
END

How can id do it

Upvotes: 1

Views: 15153

Answers (5)

Ankit Srivastava
Ankit Srivastava

Reputation: 1

select 
        dbo.getParentCode(chart_code,@target_level,LEVEL) chart_code,
        level,
        SUM(debit) debit, 
        SUM(credit) credit
        from acc_trial_balance_vw 
        where 
        convert(datetime,create_date,103) between convert(datetime, cast(@StartDate as datetime) , 103) 
        and  convert(datetime, cast(@EndDate as datetime)+'23:59:59' , 103)  
        and company_code = @company_code 
        GROUP BY chart_code, LEVEL

Upvotes: 0

Najib Razak
Najib Razak

Reputation: 71

create table #temp(company_code char(5),target_level int,StartDate char(12))
insert into #temp('','','')
select * from #temp

Upvotes: 0

Jacky Montevirgen
Jacky Montevirgen

Reputation: 317

Try inserting into #temptable after your dbname from acc_trial_balance_vw

 CREATE PROC [dbo].[aac_trial_balance_data]
 @company_code char(5),
 @target_level int,
 @StartDate char(12),
 @EndDate char(12)
 AS
 BEGIN
    (select 
        dbo.getParentCode(chart_code,@target_level,LEVEL) chart_code,
        level,
        SUM(debit) debit, 
        SUM(credit) credit
    into #TEMPTABLE -->>> Inserting here
    from acc_trial_balance_vw 

    where 
    convert(datetime,create_date,103) between convert(datetime, cast(@StartDate as datetime) , 103) 
    and  convert(datetime, cast(@EndDate as datetime)+'23:59:59' , 103)  
    and company_code = @company_code 
     GROUP BY chart_code, LEVEL 
     )
     AS

END

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

try this:

(select 
 dbo.getParentCode(chart_code,@target_level,LEVEL) chart_code,
 level,
 SUM(debit) debit, 
 SUM(credit) credit

 INTO #THIS_TEMP_TABLE

 from acc_trial_balance_vw 
 where 
 convert(datetime,create_date,103) between convert(datetime, cast(@StartDate as datetime) , 103) 
 and  convert(datetime, cast(@EndDate as datetime)+'23:59:59' , 103)  
 and company_code = @company_code 
 GROUP BY chart_code, LEVEL)

 SELECT * FROM #THIS_TEMP_TABLE
 Drop table #THIS_TEMP_TABLE

Upvotes: 0

Pream
Pream

Reputation: 537

you can create temp table, just use 

If Object_Id('Tempdb..#temp') Is Not Null
Drop Table #temp1
create table #temp(your columns)

Insert into #temp select...

or use select into #temp like 

select 
        dbo.getParentCode(chart_code,@target_level,LEVEL) chart_code,
        level,
        SUM(debit) debit, 
        SUM(credit) credit into #tempTable
    from acc_trial_balance_vw 
    where 
    convert(datetime,create_date,103) between convert(datetime, cast(@StartDate as datetime) , 103) 
    and  convert(datetime, cast(@EndDate as datetime)+'23:59:59' , 103)  
    and company_code = @company_code 
     GROUP BY chart_code, LEVEL

Upvotes: 3

Related Questions