CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2005
HEEN
HEEN

Reputation: 4721

Insert stored procedure data into temp table not working

I have created a stored procedure which takes 3 parameters. See below

ALTER PROCEDURE [dbo].[Leave_Allocation_Mar_2016]
    @Type nvarchar(10),
    @Month int,
    @Year int
AS
BEGIN
    declare @emp_card_no numeric(9)
    declare @emp_name varchar(20)
    declare @dt_of_join datetime
    declare @Total_days numeric(5,2)
    declare @Days_worked numeric(5,2)
    declare @Final_PaidDayLop numeric(5,2)
    declare @TotalRecord int
    declare @actualMonth int
    declare @actualYear int
    declare @actuallastdate varchar(20)     
 IF(@Type = 'C')
BEGIN
            Print 'Yes I am in the Current process';                        
        DECLARE daily_Allocate CURSOR FOR  
                Select distinct c.emp_card_no, c.emp_name, c.Dt_Of_Join from emp_mst c  
                    join emp_mon_day d 
                    on c.emp_card_no=d.emp_mkey 
                    WHERE Dt_Of_Join =  CAST(FLOOR(CAST( DATEADD(month, -6, GETDATE()) AS FLOAT ))AS DATETIME)                  
            OPEN daily_Allocate   
            FETCH NEXT FROM daily_Allocate INTO
            @emp_card_no, @emp_name, @Dt_Of_Join                
            WHILE @@FETCH_STATUS = 0   
            BEGIN                                           
                    select  @Total_days = Sum(total_day),@Days_worked = Sum(days_worked)
                    from emp_mon_day a      
                    where  a.emp_mkey = 2519
                    group by emp_mkey                               
                        PRINT 'Employee Card no = ' + cast(@emp_card_no as char)
                        PRINT 'Total days = ' + cast(@Total_days as char)
                        PRINT 'Days Worked = ' +    cast(@Days_worked as char)                  
                set @Final_PaidDayLop = 0;
                set @TotalRecord = 0;       

                 Select @Final_PaidDayLop =  
                    isnull(sum(days),0)
                    from  P_Emp_Del_App_Hdr c join P_Emp_Del_App_trl d on c.mkey=d.mkey       
                    where c.delete_flag='N' and app_flag='Y' and c.year = @actualYear
                   and c.emp_mkey = @emp_card_no              

            Select @TotalRecord =  ((1.75 * 6) / @Total_days) * (@Days_worked + @Final_PaidDayLop) 
                from emp_mon_day a where  a.emp_mkey = @emp_card_no group by a.emp_mkey                         

        PRINT 'Final Paid LOP '  + cast(coalesce(@Final_PaidDayLop,0) as char)  
        PRINT 'Total Record ' + cast(coalesce(@TotalRecord,0) as char)                  

                  FETCH NEXT FROM daily_Allocate INTO 
                  @emp_card_no, @emp_name, @Dt_Of_Join 
            END   
        CLOSE daily_Allocate   
        DEALLOCATE daily_Allocate
END     END

which gives me output as below

Image desc

Now what I want is, I want to insert the above data into the temp table.

I tried like below

CREATE TABLE #tmp1111
(
  Sr_no int identity(1,1),
   Current_Status nvarchar(255),
   Emp_card_no int,
   Total_days int,
   days_worked int,
   final_lop_paid int,
   total_record int
)

First I created a temp table and tried to insert the data as below

INSERT INTO #tmp1111 exec Leave_Allocation_Mar_2016 'C', '2', '2016'

It executed succesfully but when I ran the select statement there were no records in the temp table.

I am using SQL server 2005

Upvotes: 0

Views: 2683

Answers (4)

Krunal Mevada
Krunal Mevada

Reputation: 1655

Instead of @Result table variale use as per below:

CREATE TABLE #Results
(
   Emp_card_no      NUMERIC(9),
   Total_days       NUMERIC(5,2),
   Days_worked      NUMERIC(5,2),
   Final_PaidDayLop NUMERIC(5,2),
   TotalRecord      INT
)

INSERT INTO #Results
( 
    Emp_card_no,
    Total_days,
    Days_worked,
    Final_PaidDayLop
)
VALUES  
( 
    @emp_card_no ,      -- Emp_card_no - numeric
    @Total_days ,       -- Total_days - numeric
    @Days_worked ,      -- Days_worked - numeric
    @Final_PaidDayLop   -- Final_PaidDayLop - numeric
)

And after DEALLOCATE daily_Allocate insert below statement

SELECT * FROM #Results
DROP TABLE #Results

This select statement returns all rows which records are inserted in #Results temp table while executing cursor

Make those changes and Hope this one is helps you

Update:

ALTER PROCEDURE [dbo].[Leave_Allocation_Mar_2016]
    @Type   NVARCHAR(10),
    @Month  INT,
    @Year   INT
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Results
    (
       Emp_card_no      NUMERIC(9),
       Total_days       NUMERIC(5,2),
       Days_worked      NUMERIC(5,2),
       Final_PaidDayLop NUMERIC(5,2),
       TotalRecord      INT
    )

    DECLARE @emp_card_no        NUMERIC(9)
    DECLARE @emp_name           VARCHAR(20)
    DECLARE @dt_of_join         DATETIME
    DECLARE @Total_days         NUMERIC(5,2)
    DECLARE @Days_worked        NUMERIC(5,2)
    DECLARE @Final_PaidDayLop   NUMERIC(5,2)
    DECLARE @TotalRecord        INT
    DECLARE @actualMonth        INT
    DECLARE @actualYear         INT
    DECLARE @actuallastdate     VARCHAR(20)     

    IF(@Type = 'C')
    BEGIN

        PRINT 'Yes I am in the Current process';                        

        DECLARE daily_Allocate CURSOR 
        LOCAL SCROLL STATIC
        FOR        
        SELECT DISTINCT c.emp_card_no, c.emp_name, c.Dt_Of_Join FROM emp_mst c  
        JOIN emp_mon_day d ON c.emp_card_no=d.emp_mkey 
        WHERE Dt_Of_Join = 
        CAST(FLOOR(CAST( DATEADD(month, -6, GETDATE()) AS FLOAT)) AS DATETIME)                  

        OPEN daily_Allocate   
        FETCH NEXT FROM daily_Allocate INTO @emp_card_no, @emp_name, @Dt_Of_Join                

        WHILE @@FETCH_STATUS = 0   
        BEGIN                                           
            SELECT @Total_days = SUM(total_day), @Days_worked = SUM(days_worked)
            FROM emp_mon_day a      
            WHERE a.emp_mkey = 2519
            GROUP BY emp_mkey                               

            PRINT 'Employee Card no = ' + cast(@emp_card_no AS CHAR)
            PRINT 'Total days = ' + cast(@Total_days AS CHAR)
            PRINT 'Days Worked = ' + cast(@Days_worked AS CHAR)                  

            SET @Final_PaidDayLop = 0;
            SET @TotalRecord = 0;       

            SELECT @Final_PaidDayLop = ISNULL(SUM(days), 0)
            FROM  P_Emp_Del_App_Hdr c JOIN P_Emp_Del_App_trl d ON c.mkey=d.mkey       
            WHERE c.delete_flag='N' AND app_flag='Y' AND c.year = @actualYear 
            AND c.emp_mkey = @emp_card_no              

            SELECT 
            @TotalRecord = ((1.75 * 6) / @Total_days) * (@Days_worked + @Final_PaidDayLop) 
            FROM emp_mon_day a 
            WHERE  a.emp_mkey = @emp_card_no 
            GROUP BY a.emp_mkey                         

            PRINT 'Final Paid LOP '  + CAST(COALESCE(@Final_PaidDayLop, 0) AS CHAR)  
            PRINT 'Total Record ' + CAST(COALESCE(@TotalRecord, 0) AS CHAR)                  

            INSERT INTO #Results
            ( 
                Emp_card_no,
                Total_days,
                Days_worked,
                Final_PaidDayLop
            )
            VALUES  
            ( 
                @emp_card_no ,      -- Emp_card_no - numeric
                @Total_days ,       -- Total_days - numeric
                @Days_worked ,      -- Days_worked - numeric
                @Final_PaidDayLop   -- Final_PaidDayLop - numeric
            )

        FETCH NEXT FROM daily_Allocate INTO @emp_card_no, @emp_name, @Dt_Of_Join 
        END   

        CLOSE daily_Allocate   
        DEALLOCATE daily_Allocate

        SELECT * FROM #Results
        DROP TABLE #Results

    END     
END

Then EXEC Leave_Allocation_Mar_2016 'C', '2', '2016' you will get result in table output

Upvotes: 1

IVNSTN
IVNSTN

Reputation: 9299

Try this instead of cursor:

    declare @dt_of_join date

    set @dt_of_join = DATEADD(month, -6, GETDATE())

    Select 
      c.emp_card_no, c.emp_name,
      (1.75 * 6) / NullIf(d.Total_days, 0)) * (IsNull(d.Days_worked, 0) + IsNull(p.Final_PaidDayLop, 0)) as TotalRecord
    from dbo.emp_mst c  
    outer apply
    (
      select 
        sum(d.total_day) as total_days
        sum(d.days_worker) as days_worker
      from dbo.emp_mon_day d
      WHERE d.emp_mkey = c.emp_card_no
      GROUP BY d.emp_mkey
    ) d
    outer apply
    (
      select sum(cc.days) as Final_PaidDayLop
      from  P_Emp_Del_App_Hdr cc
      inner join P_Emp_Del_App_trl dd on cc.mkey=d.mkey
      where cc.delete_flag='N' and dd.app_flag='Y' 
        and cc.year = c.Dt_Of_Join
        and cc.emp_mkey = c.emp_card_no
    ) p
    where c.Dt_Of_Join = @dt_of_join

Not sure if guessed everything right (including aliases).

and use insert exec if you wish or any other way of inserting into temp table.

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

instead of printing the values, store its in local variable (or insert into table variable) and return it from Stored Procedure, then it will work

eg:-

BEGIN
....
DECLARE @v_str AS NVARCHAR(500)
SET @v_str = ''
...
SELECT @v_str = @v_str+'Yes I am in the Current process'
...
SELECT @v_str = @v_str+'Total Records'
.
.
SELECT @v_str AS result
END

Upvotes: 0

Paddy
Paddy

Reputation: 33857

There is no direct output from your stored procedure, only printing of variables. You need to return the values you want, e.g. within a table:

Note that the table is required rather than just returning variables, as you may have multiple loops within your cursor

ALTER PROCEDURE [dbo].[Leave_Allocation_Mar_2016]
    @Type nvarchar(10),
    @Month int,
    @Year int
AS
BEGIN
    DECLARE @Results TABLE (
       Emp_card_no NUMERIC(9),
       Total_days NUMERIC(5,2),
       Days_worked NUMERIC(5,2),
       Final_PaidDayLop numeric(5,2)
       TotalRecord int
    )

    declare @emp_card_no numeric(9)
    declare @emp_name varchar(20)
    declare @dt_of_join datetime
    declare @Total_days numeric(5,2)
    declare @Days_worked numeric(5,2)
    declare @Final_PaidDayLop numeric(5,2)
    declare @TotalRecord int
    declare @actualMonth int
    declare @actualYear int
    declare @actuallastdate varchar(20)     
 IF(@Type = 'C')
BEGIN
            Print 'Yes I am in the Current process';                        
        DECLARE daily_Allocate CURSOR FOR  
                Select distinct c.emp_card_no, c.emp_name, c.Dt_Of_Join from emp_mst c  
                    join emp_mon_day d 
                    on c.emp_card_no=d.emp_mkey 
                    WHERE Dt_Of_Join =  CAST(FLOOR(CAST( DATEADD(month, -6, GETDATE()) AS FLOAT ))AS DATETIME)                  
            OPEN daily_Allocate   
            FETCH NEXT FROM daily_Allocate INTO
            @emp_card_no, @emp_name, @Dt_Of_Join                
            WHILE @@FETCH_STATUS = 0   
            BEGIN                                           
                    select  @Total_days = Sum(total_day),@Days_worked = Sum(days_worked)
                    from emp_mon_day a      
                    where  a.emp_mkey = 2519
                    group by emp_mkey                               
                        PRINT 'Employee Card no = ' + cast(@emp_card_no as char)
                        PRINT 'Total days = ' + cast(@Total_days as char)
                        PRINT 'Days Worked = ' +    cast(@Days_worked as char)                  
                set @Final_PaidDayLop = 0;
                set @TotalRecord = 0;       

                 Select @Final_PaidDayLop =  
                    isnull(sum(days),0)
                    from  P_Emp_Del_App_Hdr c join P_Emp_Del_App_trl d on c.mkey=d.mkey       
                    where c.delete_flag='N' and app_flag='Y' and c.year = @actualYear
                   and c.emp_mkey = @emp_card_no              

            Select @TotalRecord =  ((1.75 * 6) / @Total_days) * (@Days_worked + @Final_PaidDayLop) 
                from emp_mon_day a where  a.emp_mkey = @emp_card_no group by a.emp_mkey                         

        PRINT 'Final Paid LOP '  + cast(coalesce(@Final_PaidDayLop,0) as char)  
        PRINT 'Total Record ' + cast(coalesce(@TotalRecord,0) as char)                  

                    INSERT INTO @Results
                            ( Emp_card_no ,
                              Total_days ,
                              Days_worked ,
                              Final_PaidDayLop
                            )
                    VALUES  ( @emp_card_no , -- Emp_card_no - numeric
                              @Total_days , -- Total_days - numeric
                              @Days_worked , -- Days_worked - numeric
                              @Final_PaidDayLop  -- Final_PaidDayLop - numeric
                            )
                  FETCH NEXT FROM daily_Allocate INTO 
                  @emp_card_no, @emp_name, @Dt_Of_Join 
            END   
        CLOSE daily_Allocate   
        DEALLOCATE daily_Allocate

    SELECT 'Yes I am in the Current process', *
    FROM @Results

END     
END

I would also recommend tidying up your formatting and consistency in your variable naming to make this easier to manually parse. Some error handling to ensure your cursor is also always closed would be useful.

Also suggest declaring your cursor as FAST_FORWARD, READ_ONLY:

What is the advantage of using FAST_FORWARD for defining a cursor?

Upvotes: 0

Related Questions