insert into temp table select .... query used in procedure hang

[Former Member]

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

[VolkerBarth]

Well, I'd try with "sa_conn_info" to find out whether the connection running the stored procedure is blocked by another connection...

[justin_willey]

can you post the procedure code?

[Former Member]

thanks for the quick response Volker..

just tried with "sa_conn_info" , No blocked connection

[Breck_Carter]

PLEASE SHOW US THE CODE!!!

Nobody can solve bugs if they can't see the code.

[Former Member]

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.

[VolkerBarth]

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

<pre>...</pre> tag

or just mark all code lines and use the "code" button (which works only for questions and answers, not for comments, obviously).

[Breck_Carter]

What do you mean "hang"? Is there an error message? What does sa_conn_info() show?

[Former Member]

it keeps running and no result set returned for long time.

[Former Member]

it keeps running and no result set returned for long time.

No error Message

sa_conn_info shows no locking

[Former Member]

I have tried using cursor instead of insert into ....select, it works.

any idea why the insert into ...select not working? very weird...

[former_SQLA_member1695007]

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.