Hi I have used temp table in a procedure and inserting row into temp table ( insert into #temptable select ..... ) in a sequence of process. I noticed the weird behaviour that the Procedure didn't returned any result-set and keep running when I executed and looks hang. But when I try to execute the select query used as a part of insert statement separately, It returned a result-set. what could be the reason why the insert into temptable is not working inside the procedure. TIA vhm Edit (MC) Here is the procedure (copied and formatted from comments below): create PROCEDURE "DBA"."r_sd_stk" @as_compcode char(2), @as_stcatcode char(5), @as_edcatcode char(5), @as_stitemno char(50), @as_editemno char(50), @as_stdate char(10), @as_eddate char(10), @as_location char(10), @an_qty_dec numeric, @an_cost_dec numeric, @as_qty_format char(20), @as_cost_format char(20), @as_total_format char(20), @ai_stpt int, @ai_stln int as declare @ls_12Date char(10), @ls_6Date char(10), @ls_3Date char(10), @ls_4Date char(10), @ls_1Date char(10), @ld_total decimal(18,6), @ls_3SODate char(10), @ls_4SODate char(10), @ls_1SODate char(10), @ls_6SODate char(10), @ls_12SODate char(10), @ls_RmSODate char(10) BEGIN create table #stockinvoiced ( item_no char(50) NULL, cat_code char(5) NULL, uom char(5) NULL, item_descr char(200) NULL, cat_descr char(50) NULL, doc_no char(30) NULL, doc_date date NULL, qty_12 decimal(18,6) NULL, qty_3 decimal(18,6) NULL, qty_1 decimal(18,6) NULL, warehouse char(50) NULL, sub_cat char(5) NULL, subcat_descr char(20) NULL ) select @ls_12Date = convert(char(10),dateadd(day,1,dateadd(month,-12,@as_eddate)),111) select @ls_3Date = convert(char(10),dateadd(day,1,dateadd(month,-3,@as_eddate)),111) select @ls_4Date = convert(char(10),dateadd(day,1,dateadd(month,-4,@as_eddate)),111) select @ls_1Date = convert(char(10),dateadd(day,1,dateadd(month,-1,@as_eddate)),111) select @ls_6Date = convert(char(10),dateadd(day,1,dateadd(month,-6,@as_eddate)),111) select @ls_4SODate = convert(char(10),dateadd(day,1,dateadd(month,-4,@as_eddate)),111) select @ls_3SODate = convert(char(10),dateadd(day,1,dateadd(month,-3,@as_eddate)),111) select @ls_1SODate = convert(char(10),dateadd(day,1,dateadd(month,-1,@as_eddate)),111) select @ls_6SODate = convert(char(10),dateadd(day,1,dateadd(month,-6,@as_eddate)),111) select @ls_12SODate = convert(char(10),dateadd(day,1,dateadd(month,-12,@as_eddate)),111) select @ls_RmSODate = convert(char(10),dateadd(month,-12,@as_eddate),111) insert into #stockinvoiced select D.item_no, A.cat_code, A.uom, A.item_descr, C.descr, M.doc_no, convert(date,M.doc_date), D.qty_order, 0,0, W.name, S.sub_cat, S.descr from ar_invmaster M, ar_invdtls D, in_item A, in_category C, in_warehouse W, in_sub_cat S where M.doc_no = D.doc_no and M.comp_code = D.comp_code and D.comp_code = A.comp_code and D.item_id = A.item_id and A.comp_code = C.comp_code and A.cat_code = C.cat_code and C.comp_code = S.comp_code and substring(C.cat_code,@ai_stpt,@ai_stln) = S.sub_cat and S.cat_pos = @ai_stpt and S.cat_len = @ai_stln and M.comp_code = W.comp_code and M.wh_id = W.wh_id and M.inv_status <> 'C' and convert(char(10),M.doc_date,111) between @ls_12Date and @as_eddate and D.qty_order <> 0 and D.item_no between @as_stitemno and @as_editemno and S.sub_cat between @as_stcatcode and @as_edcatcode and W.wh_code like @as_location and A.comp_code = @as_compcode insert into #stockinvoiced select D.item_no, A.cat_code, A.uom, A.item_descr, C.descr, M.doc_no, convert(date,M.doc_date), 0, D.qty_order, 0, W.name, S.sub_cat, S.descr from ar_invmaster M, ar_invdtls D, in_item A, in_category C, in_warehouse W, in_sub_cat S where M.doc_no = D.doc_no and M.comp_code = D.comp_code and D.comp_code = A.comp_code and D.item_id = A.item_id and A.comp_code = C.comp_code and A.cat_code = C.cat_code and C.comp_code = S.comp_code and substring(C.cat_code,@ai_stpt,@ai_stln) = S.sub_cat and S.cat_pos = @ai_stpt and S.cat_len = @ai_stln and M.comp_code = W.comp_code and M.wh_id = W.wh_id and M.inv_status <> 'C' and convert(char(10),M.doc_date,111) between @ls_3Date and @as_eddate and D.qty_order <> 0 and D.item_no between @as_stitemno and @as_editemno and S.sub_cat between @as_stcatcode and @as_edcatcode and W.wh_code like @as_location and A.comp_code = @as_compcode insert into #stockinvoiced select D.item_no, A.cat_code, A.uom, A.item_descr, C.descr, M.doc_no, convert(date,M.doc_date), 0, 0, D.qty_order, W.name, S.sub_cat, S.descr from ar_invmaster M, ar_invdtls D, in_item A, in_category C, in_warehouse W, in_sub_cat S where M.doc_no = D.doc_no and M.comp_code = D.comp_code and D.comp_code = A.comp_code and D.item_id = A.item_id and A.comp_code = C.comp_code and A.cat_code = C.cat_code and C.comp_code = S.comp_code and substring(C.cat_code,@ai_stpt,@ai_stln) = S.sub_cat and S.cat_pos = @ai_stpt and S.cat_len = @ai_stln and M.comp_code = W.comp_code and M.wh_id = W.wh_id and M.inv_status <> 'C' and convert(char(10),M.doc_date,111) between @ls_1Date and @as_eddate and D.qty_order <> 0 and D.item_no between @as_stitemno and @as_editemno and S.sub_cat between @as_stcatcode and @as_edcatcode and W.wh_code like @as_location and A.comp_code = @as_compcode Select * from #stockinvoiced END |
What I will do first check my result set. Check that the data is clean and the data types are correct. it had happen to me that one column is not in the right sequence. |
Well, I'd try with "sa_conn_info" to find out whether the connection running the stored procedure is blocked by another connection...
can you post the procedure code?
thanks for the quick response Volker..
just tried with "sa_conn_info" , No blocked connection
PLEASE SHOW US THE CODE!!!
Nobody can solve bugs if they can't see the code.
Just a point about practice...It's better to put the code in the question. If you indent all lines with 4 spaces, it gets formatted nicely by the forum software and is easier to read.
Fully agreed - particularly as we have to guess whether these two comments form one single block of code (the answer seems to be "yes")...
FWIW, instead of indenting, you can also put the whole code within a
or just mark all code lines and use the "code" button (which works only for questions and answers, not for comments, obviously).
What do you mean "hang"? Is there an error message? What does sa_conn_info() show?
it keeps running and no result set returned for long time.
it keeps running and no result set returned for long time.
No error Message
sa_conn_info shows no locking
I have tried using cursor instead of insert into ....select, it works.
any idea why the insert into ...select not working? very weird...