Insert stored procedure data into temp table not working

Question

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

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

There is no direct output from your stored procedure, only printing of variables. You need to return these items you are printing, possibly as a SELECT @VarName. However it's not clear what you are looking for, as you may have multiples of some of these inside the cursor? — Paddy
– Paddy, Commented Mar 15, 2016 at 11:41
@Paddy: I just want to insert the record into the temp table, which I am getting by printing in the stored procedure. if other than printing i get values insert into the table then let me know..hope u clear now — HEEN
– HEEN, Commented Mar 15, 2016 at 11:43
Why you execute store procedure from outside ? If only those parameter required then pass into store procedure — Krunal Mevada
– Krunal Mevada, Commented Mar 15, 2016 at 11:47

Krunal Mevada · Accepted Answer · 2016-03-16 04:55:49Z

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

You will definitely got all records which are inserted into #Result table while executing your store procedure.

Community · Accepted Answer · 2017-05-23 12:23:47Z

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?

i ran like this Select * from Results but giving error as Invalid object name 'Results'.
@coder Hope this link helpful for declare table variable value scope dba.stackexchange.com/questions/69217/…
@KrunalMevada: can u update and let me know in my case, as i m confused in that link too
There is no table 'Results' - it's called @Results and it lives within the scope of the sproc, not outside it. If you just EXEC the sproc, you should see the results, or insert them into a #Temp as you were previously and select from there.

Abdul Rasheed · Accepted Answer · 2016-03-15 12:18:43Z

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

IVNSTN · Accepted Answer · 2016-03-15 13:41:52Z

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.

Collectives™ on Stack Overflow

Insert stored procedure data into temp table not working

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Linked

Related